package com.eportal.struts.action;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.eportal.ORM.CodeView;
import com.eportal.ORM.CsSysTerminal;
import com.eportal.ORM.CsSysTerminalBak;
import com.eportal.ORM.DeputyCashInfoView;
import com.eportal.ORM.MklInfo;
import com.eportal.ORM.MklTradeInfo;
import com.eportal.ORM.MpayBlackList;
import com.eportal.ORM.MpayOrg;
import com.eportal.ORM.MpayPosAllIn;
import com.eportal.ORM.MpayTradeFee;
import com.eportal.ORM.Mpayrespcode;
import com.eportal.ORM.OutMerchantTradeList;
import com.eportal.ORM.OutMerchantTradeTotal;
import com.eportal.ORM.UserView;
import com.eportal.ORM.WtAccountInfo;
import com.eportal.ORM.WtAccountInfoId;
import com.eportal.ORM.WtPepJnls;
import com.eportal.ORM.WtPepJnlsBak;
import com.eportal.ORM.WtPepJnlsLs;
import com.eportal.core.BaseAction;
import com.eportal.core.Chars;
import com.eportal.core.Combo;
import com.eportal.core.OutMerchantTotal;
import com.eportal.core.Page;
import com.eportal.core.Trade;
import com.eportal.core.Util;
import com.eportal.service.AdminService;

public class TradeAction extends BaseAction {
	AdminService service;
	AdminService serviceMysql;
	AdminService serviceStlmpay;

	String trdType;
	String mercode;
	String seqno;
	String pepdate;
	String startdate;
	String enddate;
	String mklstate;
	String mklid;
	String dayu;
	String xiaoyu;
	String excel;

	public String getExcel() {
		return excel;
	}

	public void setExcel(String excel) {
		this.excel = excel;
	}

	public String getDayu() {
		return dayu;
	}

	public void setDayu(String dayu) {
		this.dayu = dayu;
	}

	public String getXiaoyu() {
		return xiaoyu;
	}

	public void setXiaoyu(String xiaoyu) {
		this.xiaoyu = xiaoyu;
	}

	public String getMklid() {
		return mklid;
	}

	public void setMklid(String mklid) {
		this.mklid = mklid;
	}

	public String getMklstate() {
		return mklstate;
	}

	public void setMklstate(String mklstate) {
		this.mklstate = mklstate;
	}

	public String getStartdate() {
		return startdate;
	}

	public void setStartdate(String startdate) {
		this.startdate = startdate;
	}

	public String getEnddate() {
		return enddate;
	}

	public void setEnddate(String enddate) {
		this.enddate = enddate;
	}

	public String getSeqno() {
		return seqno;
	}

	public void setSeqno(String seqno) {
		this.seqno = seqno;
	}

	public String getMercode() {
		return mercode;
	}

	public void setMercode(String mercode) {
		this.mercode = mercode;
	}

	public String getPepdate() {
		return pepdate;
	}

	public void setPepdate(String pepdate) {
		this.pepdate = pepdate;
	}

	public String getTrdType() {
		return trdType;
	}

	public void setTrdType(String trdType) {
		this.trdType = trdType;
	}

	List<Combo> combo;
	List<Chars> chars;
	List obj;

	public List getObj() {
		return obj;
	}

	public void setObj(List obj) {
		this.obj = obj;
	}

	private boolean success;
	private String message;
	private String dan;
	private String shopno;
	private String shopname;
	private String card;

	private String stlmBegdate;
	private String stlmEnddate;
	private String merCode;
	private String termId;

	public String getStlmBegdate() {
		return stlmBegdate;
	}

	public void setStlmBegdate(String stlmBegdate) {
		this.stlmBegdate = stlmBegdate;
	}

	public String getStlmEnddate() {
		return stlmEnddate;
	}

	public void setStlmEnddate(String stlmEnddate) {
		this.stlmEnddate = stlmEnddate;
	}

	public String getMerCode() {
		return merCode;
	}

	public void setMerCode(String merCode) {
		this.merCode = merCode;
	}

	public String getTermId() {
		return termId;
	}

	public void setTermId(String termId) {
		this.termId = termId;
	}

	public AdminService getServiceStlmpay() {
		return serviceStlmpay;
	}

	public void setServiceStlmpay(AdminService serviceStlmpay) {
		this.serviceStlmpay = serviceStlmpay;
	}

	public String getCard() {
		return card;
	}

	public void setCard(String card) {
		this.card = card;
	}

	public String getShopno() {
		return shopno;
	}

	public void setShopno(String shopno) {
		this.shopno = shopno;
	}

	public String getShopname() {
		return shopname;
	}

	public void setShopname(String shopname) {
		this.shopname = shopname;
	}

	public String getDan() {
		return dan;
	}

	public void setDan(String dan) {
		this.dan = dan;
	}

	public boolean isSuccess() {
		return success;
	}

	public void setSuccess(boolean success) {
		this.success = success;
	}

	public String getMessage() {
		return message;
	}

	public void setMessage(String message) {
		this.message = message;
	}

	Page page;
	Trade trade;
	String start = "0";
	String limit = "30";
	String startDate = "";
	String endDate = "";
	String termid;
	String sort;
	String dir;
	String type;
	String code;
	String year;
	String month;
	String day;
	String level;
	String id;
	String username;
	String linkname;
	String linkphone;
	String linkno;
	String address;
	String avgamt;
	String table;
	String bank;

	public String getTable() {
		return table;
	}

	public void setTable(String table) {
		this.table = table;
	}

	public String getBank() {
		return bank;
	}

	public void setBank(String bank) {
		this.bank = bank;
	}

	public String getAvgamt() {
		return avgamt;
	}

	public void setAvgamt(String avgamt) {
		this.avgamt = avgamt;
	}

	public String getShopid() {
		return shopid;
	}

	public void setShopid(String shopid) {
		this.shopid = shopid;
	}

	String shopid;

	boolean succ;

	public String getLevel() {
		return level;
	}

	public void setLevel(String level) {
		this.level = level;
	}

	public String getDay() {
		return day;
	}

	public void setDay(String day) {
		this.day = day;
	}

	public boolean getSucc() {
		return succ;
	}

	public void setSucc(boolean succ) {
		this.succ = succ;
	}

	public String getTermid() {
		return termid;
	}

	public void setTermid(String termid) {
		this.termid = termid;
	}

	public String getCardno() {
		return cardno;
	}

	public void setCardno(String cardno) {
		this.cardno = cardno;
	}

	public String getPsam() {
		return psam;
	}

	public void setPsam(String psam) {
		this.psam = psam;
	}

	String cardno;
	String psam;

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getLinkname() {
		return linkname;
	}

	public void setLinkname(String linkname) {
		this.linkname = linkname;
	}

	public String getLinkphone() {
		return linkphone;
	}

	public void setLinkphone(String linkphone) {
		this.linkphone = linkphone;
	}

	public String getLinkno() {
		return linkno;
	}

	public void setLinkno(String linkno) {
		this.linkno = linkno;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	public List<Combo> getCombo() {
		return combo;
	}

	public void setCombo(List<Combo> combo) {
		this.combo = combo;
	}

	public AdminService getServiceMysql() {
		return serviceMysql;
	}

	public List<Chars> getChars() {
		return chars;
	}

	public void setChars(List<Chars> chars) {
		this.chars = chars;
	}

	public String getYear() {
		return year;
	}

	public void setYear(String year) {
		this.year = year;
	}

	public String getMonth() {
		return month;
	}

	public void setMonth(String month) {
		this.month = month;
	}

	public void setServiceMysql(AdminService serviceMysql) {
		this.serviceMysql = serviceMysql;
	}

	public String getStartDate() {
		return startDate;
	}

	public void setStartDate(String startDate) {
		this.startDate = startDate;
	}

	public String getEndDate() {
		return endDate;
	}

	public void setEndDate(String endDate) {
		this.endDate = endDate;
	}

	public String getSort() {
		return sort;
	}

	public void setSort(String sort) {
		this.sort = sort;
	}

	public String getDir() {
		return dir;
	}

	public void setDir(String dir) {
		this.dir = dir;
	}

	public Page getPage() {
		return page;
	}

	public void setPage(Page page) {
		this.page = page;
	}

	public Trade getTrade() {
		return trade;
	}

	public void setTrade(Trade trade) {
		this.trade = trade;
	}

	public String getStart() {
		return start;
	}

	public void setStart(String start) {
		this.start = start;
	}

	public String getLimit() {
		return limit;
	}

	public void setLimit(String limit) {
		this.limit = limit;
	}

	public AdminService getService() {
		return service;
	}

	public void setService(AdminService service) {
		this.service = service;
	}

	public void tradeCheck() {
		String ip = getRequest().getRemoteAddr();
		LOG.info("check trade from out 。。。" + ip);
		startdate = startdate == null ? "" : startdate.trim();
		enddate = enddate == null ? "" : enddate.trim();
		mercode = mercode == null ? "" : mercode.trim();
		seqno = seqno == null ? "" : seqno.trim();
		String seqnosql = seqno.replace(",", "','");
		String msg = "";
		if (seqno.equals("") || mercode.equals("") || startdate.equals("")
				|| enddate.equals(""))
			msg = Util.returnRespon("HS06", "参数有缺损", null, mercode, seqno);
		else {
			MpayBlackList m = (MpayBlackList) serviceMysql
					.loadObject("from MpayBlackList where mercode = '"
							+ mercode + "' and flag = '1' ");
			if (m == null)
				msg = Util.returnRespon("HS03", "商户不存在", null, mercode, seqno);
			else {
				if (m.getIplist().indexOf(ip) != -1
						|| m.getIplist().equals("-")) {
					List tmp = service
							.query("from WtPepJnls where (pepdate between '"
									+ startdate
									+ "' and '"
									+ enddate
									+ "') and SYSEQNO in ('"
									+ seqnosql
									+ "') and substr(termidm,4) in (select termsysSequence from CsSysTerminal where projectcode = '"
									+ m.getInmercode() + "' )");
					if (tmp.size() == 0)
						msg = Util.returnRespon("HS12", "交易不存在", null, mercode,
								seqno);
					else {
						int p = seqno.split(",").length;
						if (p == tmp.size()) {
							msg = Util.returnRespon("0000", "查询成功", tmp,
									mercode, seqno);
						} else {
							String mm = "";
							String pp = "";
							for (int i = 0; i < tmp.size(); i++) {
								WtPepJnls w = (WtPepJnls) tmp.get(i);
								mm += w.getSyseqno().trim() + ",";
							}
							String ss[] = seqno.split(",");
							for (int i = 0; i < ss.length; i++) {
								if (mm.indexOf(ss[i]) == -1) {
									pp += ss[i] + ",";
								}
							}
							pp = pp.substring(0, pp.lastIndexOf(","));
							msg = Util.returnRespon("HS13", "该些流水无法查询", null,
									mercode, pp);
						}
					}
				} else {
					msg = Util.returnRespon("HS21", "IP受限，不做处理", null, mercode,
							seqno);
				}
			}
		}
		try {
			getResponse().setCharacterEncoding("GBK");
			this.getResponse().getWriter().print(msg);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public String getavg() {
		LOG.info("读取平均交易金额");
		Connection c = service.getConnection();
		avgamt = "0";
		try {
			String sql = "select ROUND(avg(tranamt)/100,2) from wt_pep_jnls_bak where tranamt > '0' and fst_sdrespn = '00' and termid = '"
					+ termid + "'";
			System.out.println(sql);
			PreparedStatement ps = c.prepareStatement(sql);
			ResultSet rset = ps.executeQuery();
			rset.next();
			avgamt = rset.getString(1);
			System.out.println(avgamt);
			success = true;
			c.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			success = false;
		}
		return SUCCESS;
	}

	public String cashaccount() {
		LOG.info("读取现金收款交易数据...");
		page = new Page();
		start = start == null ? "0" : start;
		limit = limit == null ? "30" : limit;
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String sql = " from WtAccountInfo where 1=1 ";
		if (!startDate.equals("") && !endDate.equals("")) {
			sql += " and (substr(crt_date,0,8) between '"
					+ startDate.split("T")[0].replace("-", "") + "' and '"
					+ endDate.split("T")[0].replace("-", "") + "') ";
		}
		if (m.getIsAll() == 0) {
			String ss = (String) getRequest().getSession().getAttribute(
					"termgroup");
			if (ss.indexOf("C") != -1 || ss.indexOf("c") != -1) {
				sql += " and ( concat('C',trim(OUTCDNO)) in (" + ss
						+ ") or concat('C',trim(substr(INTCDNO,5)))in (" + ss
						+ ") )";
			} else {
				sql += " and ( termid in (" + ss + ") or ORG_TERMCDE in (" + ss
						+ ") or  ORG_MERCODE in (" + ss + "))";
			}
		}
		page.setTotalProperty(service.countQuery("select count(*) " + sql));

		sql += " order by crt_date desc ";

		int s = Integer.valueOf(start);
		int l = Integer.valueOf(limit);
		int start = 1;
		if (s != 0)
			start = s / 30 + 1;
		Hashtable shop = new Hashtable();
		List shopl;
		if (m.getIsAll() == 0) {
			shopl = serviceMysql.query("from MpayOrg where shop_id = '"
					+ m.getShopId() + "' ");
		} else {
			shopl = serviceMysql.listAll("MpayOrg");
		}

		for (int i = 0; i < shopl.size(); i++) {
			MpayOrg mo = (MpayOrg) shopl.get(i);
			shop.put(mo.getTermid(), mo.getTermNick() == null ? "" : mo
					.getTermNick()
					+ " [" + mo.getTermName() + "]");
		}
		System.out.println("sql:" + sql);
		List tmp = service.query(sql, start, l);
		List t = new ArrayList();
		for (int i = 0; i < tmp.size(); i++) {
			WtAccountInfoId id = ((WtAccountInfo) tmp.get(i)).getId();
			String shopname = (String) shop.get(id.getTermid().trim());
			shopname = shopname == null ? "-" : shopname;
			id.setTermid(id.getTermid() + "|" + shopname);
			t.add(id);
		}
		System.out.println("count:" + tmp.size());
		page.setStart(++s);
		page.setLimit(l = l == 0 ? 30 : l);
		page.setRoot(t);
		if (null != page.getRoot()) {
			page.setSuccess(true);
		}
		return SUCCESS;
	}

	public String qslistinfo() {
		LOG.info("读取清算明细数据...");
		String sql = " where MER_CODE = '" + merCode + "' and TERM_ID = '"
				+ termId + "' and (PAY_DATE between '" + stlmBegdate
				+ "' and '" + stlmEnddate + "' and trdType = '" + trdType
				+ "') ";
		sql += " order by STLM_TYPE,PAY_DATE desc";
		System.out.println(sql);
		obj = serviceStlmpay.query("from OutMerchantTradeList " + sql);
		page = new Page();
		page.setRoot(obj);
		return SUCCESS;
	}

	public String qsdkinfo() {
		LOG.info("MKL代扣汇总。。。");
		int s = Integer.valueOf(start);
		int l = Integer.valueOf(limit);
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String sql = "";
		if (m.getMpayTermid().indexOf("MKL") != -1) {
			sql += " where mkl_pro in ('"
					+ m.getMpayTermid().replace(",", "','") + "') ";
		} else {
			sql += " where 1 = 1 ";
		}
		startDate = startDate == null ? "" : startDate.trim().replace("-", "");
		termid = termid == null ? "" : termid.trim();
		trdType = trdType == null ? "" : trdType;
		if (!startDate.equals("")) {
			sql += " and (BEG_DP_DATE like '" + startDate + "%') ";
		}
		if (!termid.equals("")) {
			sql += " and DP_MER_SEQNO like '%" + termid + "%' ";
		}
		if (!trdType.equals("") && !trdType.equals("9")) {
			sql += " and STATE = '" + trdType + "' ";
		}
		int start = 1;
		if (s != 0)
			start = s / 30 + 1;
		System.out.println(sql);

		List userList = serviceStlmpay.query("from DeputyCashInfoView " + sql,
				start, l);
		page = new Page();
		page.setRoot(userList);
		page.setTotalProperty(serviceStlmpay
				.countQuery("select count(*) from DeputyCashInfoView " + sql));
		page.setStart(++s);
		page.setLimit(l = l == 0 ? 30 : l);
		return SUCCESS;
	}

	public String qsinfo() {
		LOG.info("读取清算数据...");
		int s = Integer.valueOf(start);
		int l = Integer.valueOf(limit);
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String sql = " where 1=1 ";
		if (m.getIsAll() == 0) {
			String ss = (String) getRequest().getSession().getAttribute(
					"termgroup");
			if (ss.indexOf("C") != -1 || ss.indexOf("c") != -1) {
				sql += " and ( concat('C',trim(substr(TERMINAL_NO,5))) in ("
						+ ss + "))";
			} else if (m.getMpayTermid().indexOf("MKL") != -1) {
				sql += " and term_seqno in ('"
						+ m.getMpayTermid().replace(",", "','") + "') ";
			} else {
				sql += " and ( TERMINAL_NO in (" + ss + ")) ";
			}
		}
		Hashtable mklT = new Hashtable();
		List mkl = service.query("from MklInfo");
		for (int i = 0; i < mkl.size(); i++) {
			MklInfo mk = (MklInfo) mkl.get(i);
			mklT.put(mk.getMklId().trim(), mk);
		}
		Hashtable shop = new Hashtable();
		List shopl;
		if (m.getIsAll() == 0) {
			shopl = serviceMysql.query("from MpayOrg where shop_id = '"
					+ m.getShopId() + "' ");
			for (int i = 0; i < shopl.size(); i++) {
				MpayOrg mo = (MpayOrg) shopl.get(i);
				shop.put(mo.getTermid(), mo.getTermNick() == null ? "" : mo
						.getTermNick()
						+ " [" + mo.getTermName() + "]");
			}
		}

		startDate = startDate == null ? "" : startDate.trim().replace("-", "");
		endDate = endDate == null ? "" : endDate.trim().replace("-", "");
		termid = termid == null ? "" : termid.trim();
		trdType = trdType == null ? "" : trdType;
		if (!startDate.equals("") && !endDate.equals("")) {
			sql += " and (STLM_BEGDATE between '" + startDate.substring(0, 8)
					+ "' and '" + endDate.substring(0, 8) + "') ";
		}
		if (!termid.equals("")) {
			sql += " and TERMINAL_NO like '%" + termid + "%' ";
		}
		if (!trdType.equals("")) {
			sql += " and TRD_TYPE in ('" + trdType + "') ";
		}
		sql += " order by PAY_FG,STLM_BEGDATE desc";
		int start = 1;
		if (s != 0)
			start = s / 30 + 1;
		System.out.println(sql);

		List userList = serviceStlmpay.query("from OutMerchantTradeTotal "
				+ sql, start, l);
		List tmp = new ArrayList();
		for (int i = 0; i < userList.size(); i++) {
			OutMerchantTotal total = new OutMerchantTotal();
			OutMerchantTradeTotal o = (OutMerchantTradeTotal) userList.get(i);
			total.setCreateDate(o.getCreateDate());
			total.setMerCode(o.getId().getMerCode());
			total.setPayFg(o.getPayFg());
			total.setStlmAmt(o.getId().getStlmAmt());
			total.setStlmBegdate(o.getId().getStlmBegdate());
			total.setStlmEnddate(o.getStlmEnddate());
			total.setStlmFee(o.getStlmFee());
			total.setStlmType(o.getId().getStlmType());
			total.setTermId(o.getId().getTermId());

			if ("0123456789".indexOf(o.getId().getTerminalNo().charAt(0)) == -1) {
				total.setTerminalNo(o.getId().getTerminalNo().substring(4));
				MklInfo mk = (MklInfo) mklT.get(o.getId().getTerminalNo()
						.substring(4));
				if (mk != null)
					total.setShopName(mk.getMklCom());
			} else {
				total.setTerminalNo(o.getId().getTerminalNo());
				if (shop.get(o.getId().getTerminalNo()) != null) {
					total.setShopName(shop.get(o.getId().getTerminalNo()) + "");
				}
			}
			total.setTermSeqno(o.getId().getTermSeqno());
			total.setTradeAmt(o.getTradeAmt());
			total.setTradeNum(o.getTradeNum());
			total.setTrdType(o.getId().getTrdType());
			if (o.getId().getTrdType().equals("X00")) {
				total.setTradeAmt((long) 0);
				total.setStlmFee((long) 0);
			}
			tmp.add(total);
		}
		page = new Page();
		page.setRoot(tmp);
		page
				.setTotalProperty(serviceStlmpay
						.countQuery("select count(*) from OutMerchantTradeTotal "
								+ sql));
		page.setStart(++s);
		page.setLimit(l = l == 0 ? 30 : l);
		return SUCCESS;
	}

	public String mklshop() {
		LOG.info("读取商户报表_" + dayu + "_" + xiaoyu+"_"+excel);
		int s = Integer.valueOf(start);
		int l = Integer.valueOf(limit);
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String sql = "";
		String amtsql = "";
		if (m.getMpayTermid().indexOf("MKL") != -1) {
			sql += " where MKL_PRO in ('"
					+ m.getMpayTermid().replace(",", "','") + "') ";
			amtsql = "where mklcdno in (select mkl_id from mkl_info m where m.mkl_pro in ('"
					+ m.getMpayTermid().replace(",", "','") + "') ";
		} else {
			sql += " where 1 =  1";
			amtsql += " where 1 = 1 ";
		}
		startdate = startdate == null ? "" : startdate.trim();
		enddate = enddate == null ? "" : enddate.trim();
		shopname = shopname == null ? "" : shopname.trim();
		dayu = dayu == null ? "0" : dayu.trim();
		xiaoyu = xiaoyu == null ? "999999999999" : xiaoyu.trim();
		if (!shopname.equals("")) {
			sql += " and concat(MKL_COM,MKL_ID) like '%" + shopname + "%' ";
		}
		if (!startdate.equals("") && !enddate.equals("")) {
			amtsql += " and  (PEPDATE between '" + startdate + "' and '"
					+ enddate + "') ";
		}
		int c = 0;
		if (!dayu.equals("0") && !xiaoyu.equals("999999999999")) {
			amtsql += " having sum(succamt1-succamt2)/100 between '" + dayu
					+ "' and '" + xiaoyu + "' ";
			c = 1;
		}
		int start = 1;
		if (s != 0)
			start = s / 30 + 1;
		// System.out.println(sql);
		List tmp = service
				.findBySql("select mklcdno,sum(succamt1-succamt2)/100 as su from mkl_trade_card_day t "
						+ amtsql + " group by mklcdno");

		String mklc = "";
		HashMap map = new HashMap();
		for (int i = 0; i < tmp.size(); i++) {
			Object[] o = (Object[]) tmp.get(i);
			// System.out.println(o[0]+" "+o[1]);
			if (c == 1) {
				mklc += "'" + o[0] + "',";
			}
			map.put(o[0], o[1]);
		}
		if (!mklc.equals("")) {
			mklc += "''";
			sql += "and MKL_ID in (" + mklc + ")";
		}
		sql += " order by MKL_ID ";
		List userList = service.query("from MklInfo" + sql, start, l);
		List t = new ArrayList();
		short rowNum = 1;
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("new sheet");
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell = row.createCell((short) 0);
		if (excel != null) {

			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("商户编号");
			cell = row.createCell((short) 1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("商户名称");
			cell = row.createCell((short) 2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("销售品牌");
			cell = row.createCell((short) 3);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("地址");
			cell = row.createCell((short) 4);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("展位号");
			cell = row.createCell((short) 5);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("大类");
			cell = row.createCell((short) 6);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("小类");
			cell = row.createCell((short) 7);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("刷卡金额");
		}
		for (int i = 0; i < userList.size(); i++) {
			MklInfo mm = (MklInfo) userList.get(i);
			if (map.get(mm.getMklId()) != null)
				mm.setSum(map.get(mm.getMklId()) + "");
			else
				mm.setSum("0.00");
			t.add(mm);
			if (excel != null) {
				row = sheet.createRow(rowNum);
				cell = row.createCell((short) 0);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklId());
				cell = row.createCell((short) 1);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklCom());
				cell = row.createCell((short) 2);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklMark());
				cell = row.createCell((short) 3);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklAdd());
				cell = row.createCell((short) 4);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklShopid());
				cell = row.createCell((short) 5);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklClass());
				cell = row.createCell((short) 6);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklType());
				cell = row.createCell((short) 7);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getSum());

				rowNum++;

			}
		}
		if (excel != null) {
			try {
				OutputStream out = this.getResponse().getOutputStream();
				this.getResponse().setHeader(
						"Content-disposition",
						"attachment; filename=" + startDate + "_" + endDate
								+ ".xls");
				this.getResponse().setContentType(
						"application/msexcel;charset=UTF-8");
				wb.write(out);
				out.flush();
				out.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return null;
		}
		page = new Page();
		page.setRoot(t);
		page.setTotalProperty(service.countQuery("select count(*) from MklInfo"
				+ sql));
		page.setStart(++s);
		page.setLimit(l = l == 0 ? 30 : l);
		return SUCCESS;
	}

	public String mklinfo() {
		LOG.info("读取用户列表数据...");
		int s = Integer.valueOf(start);
		int l = Integer.valueOf(limit);
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String sql = "";
		if (m.getMpayTermid().indexOf("MKL") != -1) {
			sql += " where MKL_PRO in ('"
					+ m.getMpayTermid().replace(",", "','") + "') ";
		} else {
			sql += " where 1 =  1";
		}
		shopno = shopno == null ? "" : shopno.trim();
		shopname = shopname == null ? "" : shopname.trim();
		mklstate = mklstate == null ? "" : mklstate.trim();
		if (!shopno.equals("")) {
			sql += " and MKL_ID like '%" + shopno + "%' ";
		}
		if (!shopname.equals("")) {
			sql += " and MKL_COM like '%" + shopname + "%' ";
		}
		if (!mklstate.equals("")) {
			sql += " and MKL_STATE = '" + mklstate + "' ";
		}
		sql += " order by MKL_ID ";
		int start = 1;
		if (s != 0)
			start = s / 30 + 1;
		// System.out.println(sql);
		List userList = service.query("from MklInfo" + sql, start, l);
		page = new Page();
		page.setRoot(userList);
		page.setTotalProperty(service.countQuery("select count(*) from MklInfo"
				+ sql));
		page.setStart(++s);
		page.setLimit(l = l == 0 ? 30 : l);
		return SUCCESS;
	}

	public String getPosTradeInfo() {
		LOG.info("读取POS交易数据...");
		page = new Page();
		start = start == null ? "0" : start;
		limit = limit == null ? "30" : limit;
		Hashtable code = new Hashtable();
		Hashtable mercode = new Hashtable();
		Hashtable mertercode = new Hashtable();
		Hashtable termcode = new Hashtable();

		List tradeFee = serviceMysql.listAll("MpayTradeFee");
		for (int i = 0; i < tradeFee.size(); i++) {
			MpayTradeFee fee = (MpayTradeFee) tradeFee.get(i);
			if (!fee.getMercode().equals("0") && !fee.getTercode().equals("0")) {
				mertercode.put(fee.getMercode() + "," + fee.getTercode(), fee);
			} else if (!fee.getMercode().equals("0")
					&& fee.getTercode().equals("0")
					&& fee.getTrancde().equals("0")) {
				mercode.put(fee.getMercode(), fee);
			} else if (!fee.getTrancde().equals("0")
					&& fee.getMercode().equals("0")
					&& fee.getTercode().equals("0")) {
				code.put(fee.getTrancde(), fee);
			} else if (!fee.getTrancde().equals("0")
					&& !fee.getMercode().equals("0")
					&& fee.getTercode().equals("0")) {
				termcode.put(fee.getMercode() + "," + fee.getTrancde(), fee);
			}
		}
		// System.out.println(succ+" "+startDate+" "+endDate);
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String sql = "";
		String tablename = "Wt_Pep_Jnls";
		String tablename2 = tablename.replace("_", "");
		sql += " from " + tablename2 + " where TRANCDE like 'NP%'";
		if (!startDate.equals("") && !endDate.equals("")) {
			sql += " and (REVDATE between '"
					+ startDate.split("T")[0].replace("-", "") + "' and '"
					+ endDate.split("T")[0].replace("-", "") + "') ";
		} else {
			sql += " and REVDATE = '" + Util.getCurrentDate("yyyyMMdd") + "' ";
		}
		if (termid != null && !termid.equals("")) {
			sql += " and (termid like '%" + termid
					+ "%' or ORG_TERMCDE like '%" + termid
					+ "%' or INTCDNO like '%" + termid
					+ "%'  or OUTCDNO like '%" + termid + "%')";

		}
		if (succ) {
			sql += " and (FST_SDRESPN = '00' or ORG_APRESPN = '00')  ";
		}
		if (m.getIsAll() == 0) {
			String ss = (String) getRequest().getSession().getAttribute(
					"termgroup");
			if (ss.indexOf("C") != -1 || ss.indexOf("c") != -1) {
				sql += " and ( concat('C',trim(OUTCDNO)) in (" + ss
						+ ") or concat('C',trim(substr(INTCDNO,5)))in (" + ss
						+ ") )";
			} else {
				sql += " and ( termid in (" + ss + ") or ORG_TERMCDE in (" + ss
						+ ") or  ORG_MERCODE in (" + ss + "))";
			}
		}
		page.setTotalProperty(service.countQuery("select count(*) " + sql));
		String sqlsum = "select sum(TRANAMT) "
				+ sql.replace(tablename2, tablename)
				+ " and ORG_Aprespn = '00' ";

		String sqlcount = "select count(*) "
				+ sql.replace(tablename2, tablename)
				+ " and ORG_Aprespn = '00' ";
		Connection c = service.getConnection();
		String sum = "0";
		String count = "0";
		try {
			PreparedStatement ps = c.prepareStatement(sqlsum);
			ResultSet rset = ps.executeQuery();
			rset.next();
			sum = rset.getString(1);
			ps = c.prepareStatement(sqlcount);
			rset = ps.executeQuery();
			rset.next();
			count = rset.getString(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				c.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		System.out.println(start + " " + limit);
		if (sort == null)
			sql += " order by REVDATE desc,ORG_MERCODE ";
		else {
			if (sort.equals("amt"))
				sort = "TRANAMT";
			sql += " order by " + sort + " " + dir;
		}
		int s = Integer.valueOf(start);
		int l = Integer.valueOf(limit);
		int start = 1;
		if (s != 0)
			start = s / 30 + 1;
		Hashtable tableT = new Hashtable();
		List tradeT = serviceMysql.listAll("CodeView");
		for (int i = 0; i < tradeT.size(); i++) {
			CodeView mc = (CodeView) tradeT.get(i);
			tableT.put(mc.getId().getCodeid(), "[" + mc.getId().getBankName()
					+ "] " + mc.getId().getCodename());
		}

		Hashtable mklT = new Hashtable();
		List mkl = service.query("from MklInfo");
		for (int i = 0; i < mkl.size(); i++) {
			MklInfo mk = (MklInfo) mkl.get(i);
			mklT.put(mk.getMklId().trim(), mk);
		}

		Hashtable ydmT = new Hashtable();
		List ydm = serviceMysql.listAll("Mpayrespcode");
		for (int i = 0; i < ydm.size(); i++) {
			Mpayrespcode mp = (Mpayrespcode) ydm.get(i);
			ydmT.put(mp.getRespCode(), mp.getRespCode() + "["
					+ mp.getRespName() + "]");
		}

		Hashtable hjT = new Hashtable();
		List hj = serviceMysql.listAll("MpayPosAllIn");
		for (int i = 0; i < hj.size(); i++) {
			MpayPosAllIn mp = (MpayPosAllIn) hj.get(i);
			hjT.put(mp.getMercode() + "," + mp.getDate() + "," + mp.getType(),
					mp);
		}
		Hashtable shop = new Hashtable();
		List shopl;
		if (m.getIsAll() == 0) {
			shopl = serviceMysql.query("from MpayOrg where shop_id = '"
					+ m.getShopId() + "' ");
		} else {
			shopl = serviceMysql.listAll("MpayOrg");
		}
		for (int i = 0; i < shopl.size(); i++) {
			MpayOrg mo = (MpayOrg) shopl.get(i);
			shop.put(mo.getTermid(), mo.getTermNick() == null ? "" : mo
					.getTermNick()
					+ " [" + mo.getTermName() + "]");
		}
		System.out.println("sql:" + sql);
		List tmp = service.query(sql, start, l);
		List t = new ArrayList<Trade>();
		String oldmer = "";
		for (int i = 0; i < tmp.size(); i++) {
			trade = new Trade();
			WtPepJnls w;
			if (tablename.equals("Wt_Pep_Jnls")) {
				w = (WtPepJnls) tmp.get(i);
			} else {
				w = new WtPepJnls((WtPepJnlsBak) tmp.get(i));
			}
			trade.setAmt(Util.FormatSum(w.getTranamt()));
			if (w.getIntcdno() != null && w.getIntcdno().startsWith("MKL")) {
				String in = w.getIntcdno().substring(4).trim();
				System.out.println(in);
				if (mklT.get(in) != null) {
					MklInfo mk = (MklInfo) mklT.get(in);
					trade.setIncard(mk.getMklId() + "/" + mk.getMklMark() + "/"
							+ mk.getMklType() + "/" + mk.getMklAdd() + "/"
							+ mk.getMklCom());
				} else {

					trade.setIncard(in);
				}
			} else {
				trade.setIncard(w.getIntcdno());
			}
			trade.setOutcard(Util.toInCard(w.getOutcdno()));
			trade.setSeq(w.getSyseqno());
			trade.setTermid(w.getTermid());
			trade.setCount(count);
			String d = w.getId().getPepdate();
			String tt = w.getPeptime();
			trade.setSum(Util.FormatSum(sum));
			trade.setQsdate(w.getRevdate() == null ? "" : w.getRevdate()
					.substring(0, 4)
					+ "-"
					+ w.getRevdate().substring(4, 6)
					+ "-"
					+ w.getRevdate().substring(6, 8));
			trade.setTime(d.substring(0, 4) + "-" + d.substring(4, 6) + "-"
					+ d.substring(6, 8) + " " + tt.substring(0, 2) + ":"
					+ tt.substring(2, 4) + ":" + tt.substring(4, 6));
			trade.setTrace(w.getId().getTrace() + "");
			if (w.getTrancde() != null)
				trade.setType(tableT.get(w.getTrancde()) != null ? tableT.get(w
						.getTrancde())
						+ "" : w.getTrancde());
			String str = w.getOrgAprespn();
			str = str == null ? "" : str.trim();
			trade.setYdm(ydmT.get(str) != null ? (String) ydmT.get(str) : str);
			str = w.getTermid() == null ? "" : w.getTermid().trim();
			trade.setShopname(w.getTermpro());
			trade.setFee(w.getBit28());
			if (i == tmp.size() - 1) {
				t.add(trade);
				Trade trade1 = new Trade();
				if (hjT.get(oldmer.subSequence(0, 15) + ","
						+ w.getRevdate().trim() + ",查询") != null) {
					MpayPosAllIn mp = (MpayPosAllIn) hjT.get(oldmer
							.subSequence(0, 15)
							+ "," + w.getRevdate().trim() + ",查询");
					trade1.setQsdate("<span style='color:red;'>"
							+ mp.getDate().substring(0, 4) + "-"
							+ mp.getDate().substring(4, 6) + "-"
							+ mp.getDate().substring(6, 8) + "</span>");
					trade1.setType("查询");
					trade1.setOutcard("合计笔数:" + mp.getCount());
					trade1.setAmt("合计金额:" + mp.getAmt());
					trade1.setFee("合计手续费:" + mp.getSxf());
					trade1.setTermid("<span style='color:red;'>"
							+ mp.getMercode() + "</span>");
					t.add(trade1);
				}
				trade1 = new Trade();
				if (hjT.get(oldmer.subSequence(0, 15) + ","
						+ w.getRevdate().trim() + ",消费") != null) {
					MpayPosAllIn mp = (MpayPosAllIn) hjT.get(oldmer
							.subSequence(0, 15)
							+ "," + w.getRevdate().trim() + ",消费");
					trade1.setQsdate("<span style='color:red;'>"
							+ mp.getDate().substring(0, 4) + "-"
							+ mp.getDate().substring(4, 6) + "-"
							+ mp.getDate().substring(6, 8) + "</span>");
					trade1.setType("消费");
					trade1.setOutcard("合计笔数:" + mp.getCount());
					trade1.setAmt("合计金额:" + mp.getAmt());
					trade1.setFee("合计手续费:" + mp.getSxf());
					trade1.setTermid("<span style='color:red;'>"
							+ mp.getMercode() + "</span>");
					t.add(trade1);
				}
				trade1 = new Trade();
				if (hjT.get(oldmer.subSequence(0, 15) + ","
						+ w.getRevdate().trim() + ",消费撤消") != null) {
					MpayPosAllIn mp = (MpayPosAllIn) hjT.get(oldmer
							.subSequence(0, 15)
							+ "," + w.getRevdate().trim() + ",消费撤消");
					trade1.setQsdate("<span style='color:red;'>"
							+ mp.getDate().substring(0, 4) + "-"
							+ mp.getDate().substring(4, 6) + "-"
							+ mp.getDate().substring(6, 8) + "</span>");
					trade1.setType("消费撤消");
					trade1.setOutcard("合计笔数:" + mp.getCount());
					trade1.setAmt("合计金额:" + mp.getAmt());
					trade1.setFee("合计手续费:" + mp.getSxf());
					trade1.setTermid("<span style='color:red;'>"
							+ mp.getMercode() + "</span>");
					t.add(trade1);
				}
				trade1 = new Trade();
				if (hjT.get(oldmer.subSequence(0, 15) + ","
						+ w.getRevdate().trim() + ",预授完成") != null) {
					MpayPosAllIn mp = (MpayPosAllIn) hjT.get(oldmer
							.subSequence(0, 15)
							+ "," + w.getRevdate().trim() + ",预授完成");
					trade1.setQsdate("<span style='color:red;'>"
							+ mp.getDate().substring(0, 4) + "-"
							+ mp.getDate().substring(4, 6) + "-"
							+ mp.getDate().substring(6, 8) + "</span>");
					trade1.setType("预授完成");
					trade1.setOutcard("合计笔数:" + mp.getCount());
					trade1.setAmt("合计金额:" + mp.getAmt());
					trade1.setFee("合计手续费:" + mp.getSxf());
					trade1.setTermid("<span style='color:red;'>"
							+ mp.getMercode() + "</span>");
					t.add(trade1);
				}
				trade1 = new Trade();
				if (hjT.get(oldmer.subSequence(0, 15) + ","
						+ w.getRevdate().trim() + ",预授权") != null) {
					MpayPosAllIn mp = (MpayPosAllIn) hjT.get(oldmer
							.subSequence(0, 15)
							+ "," + w.getRevdate().trim() + ",预授权");
					trade1.setQsdate("<span style='color:red;'>"
							+ mp.getDate().substring(0, 4) + "-"
							+ mp.getDate().substring(4, 6) + "-"
							+ mp.getDate().substring(6, 8) + "</span>");
					trade1.setType("预授权");
					trade1.setOutcard("合计笔数:" + mp.getCount());
					trade1.setAmt("合计金额:" + mp.getAmt());
					trade1.setFee("合计手续费:" + mp.getSxf());
					trade1.setTermid("<span style='color:red;'>"
							+ mp.getMercode() + "</span>");
					t.add(trade1);
				}
				trade1 = new Trade();
				if (hjT.get(oldmer.subSequence(0, 15) + ","
						+ w.getRevdate().trim() + ",小计") != null) {
					MpayPosAllIn mp = (MpayPosAllIn) hjT.get(oldmer
							.subSequence(0, 15)
							+ "," + w.getRevdate().trim() + ",小计");
					trade1.setQsdate("<span style='color:red;'>"
							+ mp.getDate().substring(0, 4) + "-"
							+ mp.getDate().substring(4, 6) + "-"
							+ mp.getDate().substring(6, 8) + "</span>");
					trade1.setType("小计");
					trade1.setOutcard("合计笔数:" + mp.getCount());
					trade1.setAmt("合计金额:" + mp.getAmt());
					trade1.setFee("合计手续费:" + mp.getSxf());
					trade1.setTermid("<span style='color:red;'>"
							+ mp.getMercode() + "</span>");
					t.add(trade1);
				}
			} else {
				if (!oldmer.equals("")
						&& !oldmer.equals(w.getId().getOrgMercode()
								+ w.getRevdate().trim())) {
					Trade trade1 = new Trade();
					if (hjT.get(oldmer.subSequence(0, 15) + ","
							+ w.getRevdate().trim() + ",查询") != null) {
						MpayPosAllIn mp = (MpayPosAllIn) hjT.get(oldmer
								.subSequence(0, 15)
								+ "," + w.getRevdate().trim() + ",查询");
						trade1.setQsdate("<span style='color:red;'>"
								+ mp.getDate().substring(0, 4) + "-"
								+ mp.getDate().substring(4, 6) + "-"
								+ mp.getDate().substring(6, 8) + "</span>");
						trade1.setType("查询");
						trade1.setOutcard("合计笔数:" + mp.getCount());
						trade1.setAmt("合计金额:" + mp.getAmt());
						trade1.setFee("合计手续费:" + mp.getSxf());
						trade1.setTermid("<span style='color:red;'>"
								+ mp.getMercode() + "</span>");
						t.add(trade1);
					}
					trade1 = new Trade();
					if (hjT.get(oldmer.subSequence(0, 15) + ","
							+ w.getRevdate().trim() + ",消费") != null) {
						MpayPosAllIn mp = (MpayPosAllIn) hjT.get(oldmer
								.subSequence(0, 15)
								+ "," + w.getRevdate().trim() + ",消费");
						trade1.setQsdate("<span style='color:red;'>"
								+ mp.getDate().substring(0, 4) + "-"
								+ mp.getDate().substring(4, 6) + "-"
								+ mp.getDate().substring(6, 8) + "</span>");
						trade1.setType("消费");
						trade1.setOutcard("合计笔数:" + mp.getCount());
						trade1.setAmt("合计金额:" + mp.getAmt());
						trade1.setFee("合计手续费:" + mp.getSxf());
						trade1.setTermid("<span style='color:red;'>"
								+ mp.getMercode() + "</span>");
						t.add(trade1);
					}
					trade1 = new Trade();
					if (hjT.get(oldmer.subSequence(0, 15) + ","
							+ w.getRevdate().trim() + ",消费撤消") != null) {
						MpayPosAllIn mp = (MpayPosAllIn) hjT.get(oldmer
								.subSequence(0, 15)
								+ "," + w.getRevdate().trim() + ",消费撤消");
						trade1.setQsdate("<span style='color:red;'>"
								+ mp.getDate().substring(0, 4) + "-"
								+ mp.getDate().substring(4, 6) + "-"
								+ mp.getDate().substring(6, 8) + "</span>");
						trade1.setType("消费撤消");
						trade1.setOutcard("合计笔数:" + mp.getCount());
						trade1.setAmt("合计金额:" + mp.getAmt());
						trade1.setFee("合计手续费:" + mp.getSxf());
						trade1.setTermid("<span style='color:red;'>"
								+ mp.getMercode() + "</span>");
						t.add(trade1);
					}
					trade1 = new Trade();
					if (hjT.get(oldmer.subSequence(0, 15) + ","
							+ w.getRevdate().trim() + ",预授完成") != null) {
						MpayPosAllIn mp = (MpayPosAllIn) hjT.get(oldmer
								.subSequence(0, 15)
								+ "," + w.getRevdate().trim() + ",预授完成");
						trade1.setQsdate("<span style='color:red;'>"
								+ mp.getDate().substring(0, 4) + "-"
								+ mp.getDate().substring(4, 6) + "-"
								+ mp.getDate().substring(6, 8) + "</span>");
						trade1.setType("预授完成");
						trade1.setOutcard("合计笔数:" + mp.getCount());
						trade1.setAmt("合计金额:" + mp.getAmt());
						trade1.setFee("合计手续费:" + mp.getSxf());
						trade1.setTermid("<span style='color:red;'>"
								+ mp.getMercode() + "</span>");
						t.add(trade1);
					}
					trade1 = new Trade();
					if (hjT.get(oldmer.subSequence(0, 15) + ","
							+ w.getRevdate().trim() + ",预授权") != null) {
						MpayPosAllIn mp = (MpayPosAllIn) hjT.get(oldmer
								.subSequence(0, 15)
								+ "," + w.getRevdate().trim() + ",预授权");
						trade1.setQsdate("<span style='color:red;'>"
								+ mp.getDate().substring(0, 4) + "-"
								+ mp.getDate().substring(4, 6) + "-"
								+ mp.getDate().substring(6, 8) + "</span>");
						trade1.setType("预授权");
						trade1.setOutcard("合计笔数:" + mp.getCount());
						trade1.setAmt("合计金额:" + mp.getAmt());
						trade1.setFee("合计手续费:" + mp.getSxf());
						trade1.setTermid("<span style='color:red;'>"
								+ mp.getMercode() + "</span>");
						t.add(trade1);
					}
					trade1 = new Trade();
					if (hjT.get(oldmer.subSequence(0, 15) + ","
							+ w.getRevdate().trim() + ",小计") != null) {
						MpayPosAllIn mp = (MpayPosAllIn) hjT.get(oldmer
								.subSequence(0, 15)
								+ "," + w.getRevdate().trim() + ",小计");
						trade1.setQsdate("<span style='color:red;'>"
								+ mp.getDate().substring(0, 4) + "-"
								+ mp.getDate().substring(4, 6) + "-"
								+ mp.getDate().substring(6, 8) + "</span>");
						trade1.setType("小计");
						trade1.setOutcard("合计笔数:" + mp.getCount());
						trade1.setAmt("合计金额:" + mp.getAmt());
						trade1.setFee("合计手续费:" + mp.getSxf());
						trade1.setTermid("<span style='color:red;'>"
								+ mp.getMercode() + "</span>");
						t.add(trade1);
					}
				}
				t.add(trade);
				oldmer = w.getId().getOrgMercode() + w.getRevdate().trim();
			}
		}

		System.out.println("count:" + t.size());
		page.setStart(++s);
		page.setLimit(l = l == 0 ? 30 : l);
		page.setRoot(t);
		if (null != page.getRoot()) {
			page.setSuccess(true);
		}
		return SUCCESS;
	}

	public String getShTradeInfo() {
		LOG.info("读取上海交易数据...");

		page = new Page();
		start = start == null ? "0" : start;
		limit = limit == null ? "30" : limit;
		Hashtable code = new Hashtable();
		Hashtable mercode = new Hashtable();
		Hashtable mertercode = new Hashtable();
		Hashtable termcode = new Hashtable();

		String cx = "";// 撤销交易代码
		String ysq = "";// 预授权
		Hashtable tableT = new Hashtable();
		List tradeT = serviceMysql.listAll("CodeView");
		for (int i = 0; i < tradeT.size(); i++) {
			CodeView mc = (CodeView) tradeT.get(i);
			tableT.put(mc.getId().getCodeid(), "[" + mc.getId().getBankName()
					+ "] " + mc.getId().getCodename());
			if (mc.getId().getCodename().indexOf("撤销") != -1
					|| mc.getId().getCodename().indexOf("退货") != -1) {
				cx += "'" + mc.getId().getCodeid() + "',";
			}
			if (mc.getId().getCodename().endsWith("预授权")
					|| mc.getId().getCodename().endsWith("预授权撤销")) {
				ysq += "'" + mc.getId().getCodeid() + "',";
			}
		}
		cx += "''";
		ysq += "''";
		List tradeFee = serviceMysql.listAll("MpayTradeFee");
		for (int i = 0; i < tradeFee.size(); i++) {
			MpayTradeFee fee = (MpayTradeFee) tradeFee.get(i);
			if (!fee.getMercode().equals("0") && !fee.getTercode().equals("0")) {
				mertercode.put(fee.getMercode() + "," + fee.getTercode(), fee);
			} else if (!fee.getMercode().equals("0")
					&& fee.getTercode().equals("0")
					&& fee.getTrancde().equals("0")) {
				mercode.put(fee.getMercode(), fee);
			} else if (!fee.getTrancde().equals("0")
					&& fee.getMercode().equals("0")
					&& fee.getTercode().equals("0")) {
				code.put(fee.getTrancde(), fee);
			} else if (!fee.getTrancde().equals("0")
					&& !fee.getMercode().equals("0")
					&& fee.getTercode().equals("0")) {
				termcode.put(fee.getMercode() + "," + fee.getTrancde(), fee);
			}
		}
		// System.out.println(succ+" "+startDate+" "+endDate);
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String sql = "";
		String oldmer = "";
		String tablename = m.getTableName();
		String tablename2 = tablename.replace("_", "");
		sql += " from "
				+ tablename2
				+ " where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%')and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') ";
		if (!startDate.equals("") && !endDate.equals("")) {
			sql += " and (pepdate between '"
					+ startDate.split("T")[0].replace("-", "") + "' and '"
					+ endDate.split("T")[0].replace("-", "") + "') ";
		} else {
			sql += " and pepdate = '" + Util.getCurrentDate("yyyyMMdd") + "' ";
		}
		if (termid != null && !termid.equals("")) {
			sql += " and (termid like '%" + termid
					+ "%' or ORG_TERMCDE like '%" + termid
					+ "%' or INTCDNO like '%" + termid
					+ "%'  or OUTCDNO like '%" + termid + "%')";
		}
		if (dan != null && !dan.equals("")) {
			sql += " and INTCDNO like '%" + dan + "%' ";
		}
		if (succ) {
			sql += " and (FST_SDRESPN = '00' or ORG_APRESPN = '00') ";
		}
		if (m.getIsAll() == 0) {
			String ss = (String) getRequest().getSession().getAttribute(
					"termgroup");
			if (ss.indexOf("C") != -1 || ss.indexOf("c") != -1) {
				sql += " and ( concat('C',trim(OUTCDNO)) in (" + ss
						+ ") or concat('C',trim(substr(INTCDNO,5)))in (" + ss
						+ ") )";
			} else {
				sql += " and ( termid in (" + ss + ") or ORG_TERMCDE in (" + ss
						+ ") or  ORG_MERCODE in (" + ss + "))";
			}
		}
		page.setTotalProperty(service.countQuery("select count(*) " + sql));
		String sqlsum = "select  sum(case when trancde in (" + ysq
				+ ") then '0' when trancde in (" + cx
				+ ") then concat('-',tranamt)  else tranamt end ) "
				+ sql.replace(tablename2, tablename)
				+ " and ORG_Aprespn = '00' ";

		String sqlcount = "select count(*) "
				+ sql.replace(tablename2, tablename)
				+ " and ORG_Aprespn = '00' ";
		Connection c = service.getConnection();
		String sum = "0";
		String count = "0";
		try {
			System.out.println(sqlsum);
			PreparedStatement ps = c.prepareStatement(sqlsum);
			ResultSet rset = ps.executeQuery();
			rset.next();
			sum = rset.getString(1);
			ps = c.prepareStatement(sqlcount);
			rset = ps.executeQuery();
			rset.next();
			count = rset.getString(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(start + " " + limit);
		if (sort == null)
			sql += " order by pepdate desc,peptime desc ";
		else {
			if (sort.equals("amt"))
				sort = "TRANAMT";
			sql += " order by " + sort + " " + dir;
		}
		int s = Integer.valueOf(start);
		int l = Integer.valueOf(limit);
		int start = 1;
		if (s != 0)
			start = s / 30 + 1;

		Hashtable mklT = new Hashtable();
		List mkl = service.query("from MklInfo");
		for (int i = 0; i < mkl.size(); i++) {
			MklInfo mk = (MklInfo) mkl.get(i);
			mklT.put(mk.getMklId().trim(), mk);
		}

		Hashtable ydmT = new Hashtable();
		List ydm = serviceMysql.listAll("Mpayrespcode");
		for (int i = 0; i < ydm.size(); i++) {
			Mpayrespcode mp = (Mpayrespcode) ydm.get(i);
			ydmT.put(mp.getRespCode(), mp.getRespCode() + "["
					+ mp.getRespName() + "]");
		}
		Hashtable hjT = new Hashtable();
		List hj = serviceMysql.listAll("MpayPosAllIn");
		for (int i = 0; i < hj.size(); i++) {
			MpayPosAllIn mp = (MpayPosAllIn) hj.get(i);
			hjT.put(mp.getMercode() + "," + mp.getDate() + "," + mp.getType(),
					mp);
		}
		Hashtable shop = new Hashtable();
		List shopl;
		if (m.getIsAll() == 0) {
			shopl = serviceMysql.query("from MpayOrg where shop_id = '"
					+ m.getShopId() + "' ");
		} else {
			shopl = serviceMysql.listAll("MpayOrg");
		}
		for (int i = 0; i < shopl.size(); i++) {
			MpayOrg mo = (MpayOrg) shopl.get(i);
			shop.put(mo.getTermid(), mo.getTermNick() == null ? "" : mo
					.getTermNick()
					+ " [" + mo.getTermName() + "]");
		}
		System.out.println("sql:" + sql);
		List tmp = service.query(sql, start, l);
		List t = new ArrayList<Trade>();
		for (int i = 0; i < tmp.size(); i++) {
			trade = new Trade();
			WtPepJnls w;
			if (tablename.equals("Wt_Pep_Jnls")) {
				w = (WtPepJnls) tmp.get(i);
			} else {
				w = new WtPepJnls((WtPepJnlsBak) tmp.get(i));
			}
			if (cx.indexOf(w.getTrancde().trim()) != -1) {
				trade.setAmt("-" + Util.FormatSum(w.getTranamt()));
			} else
				trade.setAmt(Util.FormatSum(w.getTranamt()));
			if (w.getIntcdno() != null && w.getIntcdno().startsWith("MKL")) {
				String in = w.getIntcdno().substring(4).trim();
				System.out.println(in);
				if (mklT.get(in) != null) {
					MklInfo mk = (MklInfo) mklT.get(in);
					trade.setIncard(mk.getMklId() + "/" + mk.getMklMark() + "/"
							+ mk.getMklType() + "/" + mk.getMklAdd() + "/"
							+ mk.getMklCom());
				} else {

					trade.setIncard(in);
				}
			} else {
				trade.setIncard(w.getIntcdno());
			}
			trade.setOutcard(Util.toInCard(w.getOutcdno()));

			trade.setSeq(w.getSyseqno());
			trade.setTermid(w.getId().getOrgMercode()
					+ w.getId().getOrgTermcde());
			trade.setCount(count);
			if (w.getRevdate() != null && w.getRevdate().startsWith("20")) {
				trade.setQsdate(w.getRevdate() == null ? "-" : w.getRevdate()
						.substring(0, 4)
						+ "-"
						+ w.getRevdate().substring(4, 6)
						+ "-"
						+ w.getRevdate().substring(6, 8));
			} else
				trade.setQsdate("-");
			String d = w.getId().getPepdate();
			String tt = w.getPeptime();
			trade.setSum(Util.FormatSum(sum));
			trade.setTime(d.substring(0, 4) + "-" + d.substring(4, 6) + "-"
					+ d.substring(6, 8) + " " + tt.substring(0, 2) + ":"
					+ tt.substring(2, 4) + ":" + tt.substring(4, 6));
			trade.setTrace(w.getId().getTrace() + "");
			if (w.getTrancde() != null)
				trade.setType(tableT.get(w.getTrancde()) != null ? tableT.get(w
						.getTrancde())
						+ "" : w.getTrancde());
			String str = w.getOrgAprespn();
			if (w.getIntcdno() != null && !w.getIntcdno().startsWith("-")
					&& w.getIntcdno().indexOf("#") != -1) {
				// trade.setOutcard(w.getIntcdno());
				// trade.setType(trade.getType().replace("消费", "资金归集"));
				// c = service.getConnection();
				String shopid = "";
				String shoptime = "";
				String shopflag = "";
				try {
					PreparedStatement ps = c
							.prepareStatement("select t.t_cust_number,to_char(t.ordered_date,'yyyy-mm-dd hh24:mm:ss'),decode(t.processed_flag,1,'已完成','未完成') from bank_intf_orders t where bill_number = '"
									+ w.getIntcdno().trim() + "'");
					ResultSet rset = ps.executeQuery();
					rset.next();
					shopid = rset.getString(1);
					shoptime = rset.getString(2);
					shopflag = rset.getString(3);
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				trade.setShopid(shopid);
				trade.setShoptime(shoptime);
				trade.setFlag(shopflag);
			}
			str = str == null ? "" : str.trim();
			if (str.equals("00") && ysq.indexOf(w.getTrancde().trim()) == -1) {
				// 计算手续费
				if (termcode.get(w.getTermid().trim() + ","
						+ w.getTrancde().trim()) != null)
					trade.setFee(getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) termcode.get(w.getTermid().trim()
									+ "," + w.getTrancde().trim())));
				else if (mertercode.get(w.getId().getOrgMercode().trim() + ","
						+ w.getId().getOrgTermcde()) != null)
					trade.setFee(getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) mertercode.get(w.getId()
									.getOrgMercode().trim()
									+ "," + w.getId().getOrgTermcde())));
				else if (mercode.get(w.getId().getOrgMercode().trim()) != null)
					trade.setFee(getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) mercode.get(w.getId()
									.getOrgMercode().trim())));
				else if (code.get(w.getTrancde().trim()) != null)
					trade.setFee(getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) code.get(w.getTrancde().trim())));
				else if (termcode.get(w.getId().getOrgTermcde().trim() + ","
						+ w.getTrancde().trim()) != null)
					trade.setFee(getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) termcode.get(w.getId()
									.getOrgTermcde().trim()
									+ "," + w.getTrancde().trim())));
				else
					trade.setFee("-");
			}
			trade.setYdm(ydmT.get(str) != null ? (String) ydmT.get(str) : str);
			str = w.getTermid() == null ? "" : w.getTermid().trim();
			trade
					.setShopname(shop.get(str) != null ? (String) shop.get(str)
							: shop.get(w.getId().getOrgTermcde()) != null ? (String) shop
									.get(w.getId().getOrgTermcde())
									: "-");
			t.add(trade);
		}

		try {
			c.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("count:" + t.size());
		page.setStart(++s);
		page.setLimit(l = l == 0 ? 30 : l);
		page.setRoot(t);
		if (null != page.getRoot()) {
			page.setSuccess(true);
		}
		return SUCCESS;
	}

	public String getTradeInfo() {
		LOG.info("读取交易数据...");
		page = new Page();
		start = start == null ? "0" : start;
		limit = limit == null ? "30" : limit;
		System.out
				.println(table + " " + startDate + " " + endDate + " " + bank);
		Hashtable code = new Hashtable();
		Hashtable mercode = new Hashtable();
		Hashtable mertercode = new Hashtable();
		Hashtable termcode = new Hashtable();
		String cx = "";// 撤销交易代码
		String ysq = "";// 预授权
		Hashtable tableT = new Hashtable();
		List tradeT = serviceMysql.listAll("CodeView");
		for (int i = 0; i < tradeT.size(); i++) {
			CodeView mc = (CodeView) tradeT.get(i);
			tableT.put(mc.getId().getCodeid(), "[" + mc.getId().getBankName()
					+ "] " + mc.getId().getCodename());
			if (mc.getId().getCodename().indexOf("撤销") != -1
					|| mc.getId().getCodename().indexOf("退货") != -1) {
				cx += "'" + mc.getId().getCodeid() + "',";
			}
			if (mc.getId().getCodename().endsWith("预授权")
					|| mc.getId().getCodename().endsWith("预授权撤销")) {
				ysq += "'" + mc.getId().getCodeid() + "',";
			}
		}
		cx += "''";
		ysq += "''";

		Hashtable mklT = new Hashtable();
		List mkl = service.query("from MklInfo");
		for (int i = 0; i < mkl.size(); i++) {
			MklInfo mk = (MklInfo) mkl.get(i);
			mklT.put(mk.getMklId().trim(), mk);
		}
		List tradeFee = serviceMysql.listAll("MpayTradeFee");
		for (int i = 0; i < tradeFee.size(); i++) {
			MpayTradeFee fee = (MpayTradeFee) tradeFee.get(i);
			if (!fee.getMercode().equals("0") && !fee.getTercode().equals("0")) {
				mertercode.put(fee.getMercode() + "," + fee.getTercode(), fee);
			} else if (!fee.getMercode().equals("0")
					&& fee.getTercode().equals("0")
					&& fee.getTrancde().equals("0")) {
				mercode.put(fee.getMercode(), fee);
			} else if (!fee.getTrancde().equals("0")
					&& fee.getMercode().equals("0")
					&& fee.getTercode().equals("0")) {
				code.put(fee.getTrancde(), fee);
			} else if (!fee.getTrancde().equals("0")
					&& !fee.getMercode().equals("0")
					&& fee.getTercode().equals("0")) {
				termcode.put(fee.getMercode() + "," + fee.getTrancde(), fee);
			}
		}

		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String sql = "";
		String tablename;
		if (table != null && !table.equals("")) {
			tablename = table;
		} else {
			tablename = m.getTableName();
		}
		if (bank != null && bank.equals("mkl")) {
			tablename = "Mkl_Trade_Info";
		}
		String tablename2 = tablename.replace("_", "");

		sql += " from "
				+ tablename2
				+ " where (TRANAMT between '000000000001' and '999999999999') and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') ";
		if (!startDate.equals("") && !endDate.equals("")) {
			sql += " and (pepdate between '"
					+ startDate.split("T")[0].replace("-", "") + "' and '"
					+ endDate.split("T")[0].replace("-", "") + "') ";
		} else {
			sql += " and pepdate = '" + Util.getCurrentDate("yyyyMMdd") + "' ";
		}
		if (termid != null && !termid.equals("")) {
			if (bank != null && bank.equals("mkl")) {
				sql += " and concat(MKL_COM,MKL_ID,MKL_MARK) like '%" + termid
						+ "%' ";
			} else {
				sql += " and (termid like '%" + termid
						+ "%' or ORG_TERMCDE like '%" + termid
						+ "%' or INTCDNO like '%" + termid
						+ "%'  or OUTCDNO like '%" + termid
						+ "%' or ORG_MERCODE like '%" + termid + "%' )";
			}
		}
		if (card != null && !card.equals("")) {
			if (bank != null && bank.equals("mkl")) {
				sql += " and MKLCDNO like '%" + card + "%' ";
			}
		}
		if (succ) {
			if (bank != null && bank.equals("mkl")) {
				sql += " and (ORG_APRESPN = '00') ";
			} else {
				sql += " and (FST_SDRESPN = '00' or ORG_APRESPN = '00') ";
			}
		}
		Hashtable shop = new Hashtable();
		if (bank != null && !bank.equals("")) {
			String b = "";
			if (bank.equals("hnxy")) {
				b = "河南兴业";
			} else if (bank.equals("hzxy")) {
				sql += " and trancde in ('XOV','XOF') ";
			} else if (bank.equals("shms")) {
				sql += " and trancde = 'PP1' ";
			} else if (bank.equals("ttkd")) {
				// b = "天天物流 ' or term_name  = '易生分子公司";
				sql += " and (trancde = 'NT1' or ORG_TERMCDE in (select bangdingtermno from CsSysTerminal  where SURFACECODE in ('023001','073101','057401','041101','057101','035101','029001','089801','059201','020001','043101','093101','071701','053101','087101','022001','025001','021002','053201','099101','028001','085101','022002','029002','035102','010001','023002','024001','053102','075501','027001','037103')) ) ";
				// b = "天天物流";
			} else if (bank.equals("shgd")) {
				// b = "上海光大";
				sql += " and trancde = '26L' ";
			} else if (bank.equals("hnzx")) {
				sql += " and trancde = '508' ";
			}

			List tmp = serviceMysql.query(" from MpayOrg where (term_name = '"
					+ b + "')");
			String termorg = "";
			for (int i = 0; i < tmp.size(); i++) {
				MpayOrg mm = (MpayOrg) tmp.get(i);
				termorg += "'" + mm.getTermid() + "',";
				shop.put(mm.getTermid(), mm.getTermNick() == null ? "" : mm
						.getTermNick()
						+ " [" + mm.getTermName() + "]");
			}
			termorg += "''";
			String ss = (String) getRequest().getSession().getAttribute(
					"termgroup");
			if (!b.equals("") && !bank.equals("mkl")) {
				sql += " and (termid in (" + termorg + ") or ORG_TERMCDE in ("
						+ termorg + ")  )";
			}
			if (bank.equals("mkl")) {
				if (ss.indexOf("C") != -1) {
					sql += " and concat('C',trim(mklcdno)) in (" + ss + ")";
				} else {
					sql += " and  ORG_TERMCDE in (" + ss + ") ";
				}
			}
		} else {
			if (m.getIsAll() == 0) {
				String ss = (String) getRequest().getSession().getAttribute(
						"termgroup");
				if (ss.indexOf("C") != -1 || ss.indexOf("c") != -1) {
					sql += " and ( concat('C',trim(OUTCDNO)) in (" + ss
							+ ") or concat('C',trim(substr(INTCDNO,5)))in ("
							+ ss + ") )";
				} else {
					sql += " and ( termid in (" + ss + ") or ORG_TERMCDE in ("
							+ ss + ") or  ORG_MERCODE in (" + ss + "))";
				}
			}
		}
		// System.out.println(sql);
		page.setTotalProperty(service.countQuery("select count(*) " + sql));
		String sqlsum = "select  sum(case  when trancde in ("
				+ ysq
				+ ") then '0'  when trancde in ("
				+ cx
				+ ") then concat('-',tranamt)  else tranamt end ) "
				+ sql.replace(tablename2, tablename).replace("CsSysTerminal",
						"cs_sys_terminal")
				+ " and (FST_SDRESPN = '00' or ORG_APRESPN = '00') ";
		String sqlcount = "select count(*) "
				+ sql.replace(tablename2, tablename).replace("CsSysTerminal",
						"cs_sys_terminal")
				+ " and (FST_SDRESPN = '00' or ORG_APRESPN = '00') ";
		if (bank != null && bank.equals("mkl")) {
			sqlsum = sqlsum.replace("FST_SDRESPN = '00' or", "");
			sqlcount = sqlcount.replace("FST_SDRESPN = '00' or", "");
		}
		Connection c = service.getConnection();
		String sum = "0";
		String count = "0";
		try {
			System.out.println(sqlsum);
			System.out.println(sqlcount);
			PreparedStatement ps = c.prepareStatement(sqlsum);
			ResultSet rset = ps.executeQuery();
			rset.next();
			sum = rset.getString(1);
			ps = c.prepareStatement(sqlcount);
			rset = ps.executeQuery();
			rset.next();
			count = rset.getString(1);
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				c.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		System.out.println(start + " " + limit);
		if (sort == null)
			sql += " order by pepdate desc,peptime desc ";
		else {
			if (sort.equals("amt")) {
				sort = "TRANAMT";
				sql += " order by " + sort + " " + dir;
			}
		}
		int s = Integer.valueOf(start);
		int l = Integer.valueOf(limit);
		int start = 1;
		if (s != 0)
			start = s / 30 + 1;

		Hashtable ydmT = new Hashtable();
		List ydm = serviceMysql.listAll("Mpayrespcode");
		for (int i = 0; i < ydm.size(); i++) {
			Mpayrespcode mp = (Mpayrespcode) ydm.get(i);
			ydmT.put(mp.getRespCode(), mp.getRespCode() + "["
					+ mp.getRespName() + "]");
		}

		List shopl;
		if (m.getIsAll() == 0) {
			shopl = serviceMysql.query("from MpayOrg where shop_id = '"
					+ m.getShopId() + "' ");
		} else {
			shopl = serviceMysql.listAll("MpayOrg");
		}
		for (int i = 0; i < shopl.size(); i++) {
			MpayOrg mo = (MpayOrg) shopl.get(i);
			shop.put(mo.getTermid(), mo.getTermNick() == null ? "" : mo
					.getTermNick()
					+ " [" + mo.getTermName() + "]");
		}
		System.out.println("sql:" + sql);
		List tmp = service.query(sql, start, l);
		List t = new ArrayList<Trade>();
		for (int i = 0; i < tmp.size(); i++) {
			trade = new Trade();
			WtPepJnls w;
			if (tablename.equals("Wt_Pep_Jnls")) {
				w = (WtPepJnls) tmp.get(i);
			} else if (tablename.equals("Wt_Pep_Jnls_Ls")) {
				w = new WtPepJnls((WtPepJnlsLs) tmp.get(i));
			} else if (tablename.equals("Mkl_Trade_Info")) {
				w = new WtPepJnls((MklTradeInfo) tmp.get(i));
			} else {
				w = new WtPepJnls((WtPepJnlsBak) tmp.get(i));
			}
			if (cx.indexOf(w.getTrancde().trim()) != -1) {
				trade.setAmt("-" + Util.FormatSum(w.getTranamt()));
			} else
				trade.setAmt(Util.FormatSum(w.getTranamt()));
			String inCard = "";
			if (w.getIntcdno() != null && w.getIntcdno().startsWith("MKL")) {
				String in = w.getIntcdno().substring(4).trim();
				System.out.println(in);
				if (mklT.get(in) != null) {
					MklInfo mk = (MklInfo) mklT.get(in);
					inCard = mk.getMklId() + "/" + mk.getMklMark() + "/"
							+ mk.getMklType() + "/" + mk.getMklAdd() + "/"
							+ mk.getMklCom();
				} else {
					inCard = in;
				}
			} else {
				inCard = Util.formatString(w.getIntcdno());
			}
			trade.setIncard(inCard);
			if (m.getMpayUserid().equals("BJJMJ")) {
				trade.setOutcard(w.getOutcdno());
			} else {
				trade.setOutcard(Util.toInCard(w.getOutcdno()));
			}

			trade.setSeq(w.getSyseqno());
			trade.setTermid(w.getTermid());
			trade.setCount(count);
			String d = w.getId().getPepdate();
			String tt = w.getPeptime();
			trade.setSum(Util.FormatSum(sum));
			trade.setTime(d.substring(0, 4) + "-" + d.substring(4, 6) + "-"
					+ d.substring(6, 8) + " " + tt.substring(0, 2) + ":"
					+ tt.substring(2, 4) + ":" + tt.substring(4, 6));
			trade.setTrace(w.getId().getTrace() + "");
			if (w.getTrancde() != null)
				trade.setType(tableT.get(w.getTrancde()) != null ? tableT.get(w
						.getTrancde())
						+ "" : w.getTrancde());
			String str = w.getFstSdrespn();
			if (str == null) {
				str = w.getOrgAprespn();
			}
			if (w.getTrancde() != null) {
				if (w.getTrancde().trim().equals("127")
						|| w.getTrancde().trim().equals("WOI")
						|| w.getTrancde().trim().equals("WOL")) {
					str = w.getNxtSdrespn();
				}
			}
			if (str == null)
				str = "";
			str = str.trim();
			if (ysq.indexOf(w.getTrancde().trim()) == -1 && str.equals("00")
					&& (bank == null || bank.equals(""))) {
				// 计算手续费
				if (termcode.get(w.getTermid().trim() + ","
						+ w.getTrancde().trim()) != null)
					trade.setFee(getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) termcode.get(w.getTermid().trim()
									+ "," + w.getTrancde().trim())));
				else if (mertercode.get(w.getId().getOrgMercode().trim() + ","
						+ w.getId().getOrgTermcde()) != null)
					trade.setFee(getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) mertercode.get(w.getId()
									.getOrgMercode().trim()
									+ "," + w.getId().getOrgTermcde())));
				else if (mercode.get(w.getId().getOrgMercode().trim()) != null)
					trade.setFee(getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) mercode.get(w.getId()
									.getOrgMercode().trim())));
				else if (termcode.get(w.getId().getOrgTermcde().trim() + ","
						+ w.getTrancde().trim()) != null)
					trade.setFee(getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) termcode.get(w.getId()
									.getOrgTermcde().trim()
									+ "," + w.getTrancde().trim())));
				else if (code.get(w.getTrancde().trim()) != null)
					trade.setFee(getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) code.get(w.getTrancde().trim())));

				else
					trade.setFee("-");
			}
			trade.setYdm(ydmT.get(str) != null ? (String) ydmT.get(str) : str);
			str = w.getTermid() == null ? "" : w.getTermid().trim();
			trade
					.setShopname(shop.get(str) != null ? (String) shop.get(str)
							: shop.get(w.getId().getOrgTermcde()) != null ? (String) shop
									.get(w.getId().getOrgTermcde())
									: "-");
			trade.setMkladd(w.getTrntime());
			trade.setMklclass(w.getTrack3());
			trade.setMklcomm(w.getTrndate());
			trade.setMklmark(w.getTermtrc());
			trade.setMklshopid(w.getTrack2());
			trade.setMklsite(w.getSendcde());
			trade.setMkltype(w.getPoitcde());
			trade.setTermcde(w.getId().getOrgTermcde());
			t.add(trade);
		}

		System.out.println("count:" + t.size());
		page.setStart(++s);
		page.setLimit(l = l == 0 ? 30 : l);
		page.setRoot(t);
		if (null != page.getRoot()) {
			page.setSuccess(true);
		}
		return SUCCESS;
	}

	public String getTradeType() {
		LOG.info("读取交易类型...");
		page = new Page();
		combo = new ArrayList<Combo>();
		Combo c = new Combo();
		c.setId("测试1");
		c.setValue("测试1");
		combo.add(c);
		Combo c2 = new Combo();
		c2.setId("测试12");
		c2.setValue("测试12");
		combo.add(c2);
		page.setRoot(combo);
		return SUCCESS;
	}

	public String mklchange() {
		try {
			MklInfo m = (MklInfo) service.loadById(MklInfo.class, mklid);
			m.setMklState(mklstate);
			if (mklstate.equals("9"))
				m.setMklDelDate(new Timestamp(new Date().getTime()));
			if (mklstate.equals("0"))
				m.setMklLtDate(new Timestamp(new Date().getTime()));
			service.saveOrUpdate(m);
			message = "更新数据成功！";
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			message = "更新数据失败！";
		}
		return SUCCESS;
	}

	public String getTradeSheet() {
		LOG.info("获得交易图表");
		// String sql1 = "";
		// String avg = "";
		// UserView m = (UserView) getRequest().getSession().getAttribute(
		// "mpay_user");
		// String tablename = m.getTableName();
		// String tablename2 = tablename.replace("_", "");
		// Hashtable ta = new Hashtable();
		// if (m.getIsAll() == 0) {
		// String ss = (String) getRequest().getSession().getAttribute(
		// "termgroup");
		// sql1 = " and termid in (" + ss + ") ";
		// avg = " and termid in (" + ss + ") ";
		// }
		// chars = new ArrayList<Chars>();
		//
		// if (code.equals("0"))
		// code = "收款";
		// else if (code.equals("1"))
		// code = "付款";
		// else {
		// code = "";
		// }
		// String codegroup = "";
		// List codelist;
		// if (!code.equals(""))
		// codelist = serviceMysql.query("from MpayCode where codename = '"
		// + code + "'");
		// else
		// codelist = serviceMysql.listAll("MpayCode");
		// for (int i = 0; i < codelist.size(); i++) {
		// MpayCode mm = (MpayCode) codelist.get(i);
		// codegroup += "'" + mm.getCodeid() + "',";
		// }
		// codegroup = codegroup.substring(0, codegroup.lastIndexOf(","));
		// String sql = "";
		// // and trancde in ("+codegroup+")
		//
		// avg = "select FLOOR(AVG(t.tranamt)/100) from "
		// + tablename
		// + " t where (tranamt between '000000000001' and '999999999999') "
		// + avg + " and t.fst_sdrespn = '00'  and pepdate like '" + year
		// + month + "%'and trancde in (" + codegroup + ")";
		// if (type.equals("0")) {
		// sql += "select pepdate,count(*)*ABC,sum(t.tranamt)/100 from "
		// + tablename
		// + " t where (tranamt between '000000000001' and '999999999999') "
		// + sql1 + " and t.fst_sdrespn = '00'  and pepdate like '"
		// + year + month + "%' and trancde in (" + codegroup
		// + ") group by pepdate order by pepdate";
		// } else if (type.equals("1")) {
		// sql +=
		// "select substr(t.pepdate,0,6),count(*)*ABC,sum(t.tranamt)/100 from "
		// + tablename
		// + " t where (tranamt between '000000000001' and '999999999999') "
		// + sql1
		// + " and t.fst_sdrespn = '00' and substr(t.pepdate,0,6) like '"
		// + year
		// + "%'  and trancde in ("
		// + codegroup
		// + ") group by substr(t.pepdate,0,6) order by substr(t.pepdate,0,6)";
		// }
		// Connection c = service.getConnection();
		// try {
		// System.out.println(avg);
		// PreparedStatement ps = c.prepareStatement(avg);
		// ResultSet rset = ps.executeQuery();
		// rset.next();
		// String str = rset.getString(1);
		// str = Integer.valueOf(str) / 2 + "";
		// sql = sql.replace("ABC", str);
		// System.out.println(sql);
		// ps = c.prepareStatement(sql);
		// rset = ps.executeQuery();
		// while (rset.next()) {
		// ta.put(Integer.valueOf(rset.getString(1).substring(6, 8)), rset
		// .getString(3)
		// + "," + rset.getString(2));
		// }
		//
		// for (int i = 1; i <= Util.getDayByMouth(year + month); i++) {
		// Chars cc = new Chars();
		// if (ta.get(i) != null) {
		// String data = (String) ta.get(i);
		// cc.setName(i + "日");
		// cc.setViews(data.split(",")[0]);
		// cc.setVisits(data.split(",")[1]);
		// } else {
		// cc.setName(i + "日");
		// cc.setViews("0");
		// cc.setVisits("0");
		// }
		// cc.setPre(str);
		// chars.add(cc);
		// if ((year + month + Util.addZero("" + i, 2)).equals(Util
		// .getCurrentDate("yyyyMMdd"))) {
		// break;
		// }
		// }
		// } catch (SQLException e) {
		// // TODO Auto-generated catch block
		// e.printStackTrace();
		// 
		if (shopid != null) {
			shopid = Long.valueOf(shopid) + "";
			if (shopid.equals("0"))
				shopid = null;
		}
		if (termid != null) {
			if (type.equals("0")) {
				chars = getMouthTrade(startDate, endDate, termid);
			}
		} else {
			if (type.equals("0")) {
				chars = getMouthTrade(startDate, endDate);
			} else if (type.equals("2")) {
				chars = getDayTrade(startDate);
			} else if (type.equals("1")) {
				chars = getYearTrade(startDate, endDate);
			} else if (type.equals("3")) {
				chars = getYonY(startDate, endDate);
			} else if (type.equals("4")) {
				chars = getMonM(startDate, endDate);
			} else if (type.equals("demo0")) {
				chars = getMouthTrade_demo(startDate, endDate, shopid);
			} else if (type.equals("demo1")) {
				chars = getYearTrade_demo(startDate, endDate, shopid);
			} else if (type.equals("demo4")) {
				chars = getMonM_demo(startDate, endDate, shopid);
			}
		}
		System.out.println(type + " " + startDate + " " + endDate + " "
				+ termid);
		return SUCCESS;
	}

	public String findInfo() {
		LOG.info("查找用户资料");
		List tmp = service
				.query("from CsSysTerminalBak where concat(areano,terminalno) = '"
						+ id + "' and state = '1' order by updatedt desc");
		if (tmp.size() > 0) {
			CsSysTerminalBak c = (CsSysTerminalBak) tmp.get(0);
			address = c.getInstalladdress();
			psam = c.getSamno();
			cardno = c.getBangdingaccounts();
		} else {
			tmp = service
					.query("from CsSysTerminal where concat(areano,terminalno) = '"
							+ id + "' and state = '1' order by updatedt desc");
			if (tmp.size() > 0) {
				CsSysTerminal c = (CsSysTerminal) tmp.get(0);
				address = c.getInstalladdress();
				psam = c.getSamno();
				cardno = c.getBangdingaccounts();
			}
		}
		return SUCCESS;
	}
	
	public InputStream excelToMklShop() {
		LOG.info("读取商户报表_" + dayu + "_" + xiaoyu+"_"+excel);
		int s = Integer.valueOf(start);
		int l = Integer.valueOf(limit);
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String sql = "";
		String amtsql = "";
		if (m.getMpayTermid().indexOf("MKL") != -1) {
			sql += " where MKL_PRO in ('"
					+ m.getMpayTermid().replace(",", "','") + "') ";
			amtsql = "where mklcdno in (select mkl_id from mkl_info m where m.mkl_pro in ('"
					+ m.getMpayTermid().replace(",", "','") + "') ";
		} else {
			sql += " where 1 =  1";
			amtsql += " where 1 = 1 ";
		}
		startdate = startdate == null ? "" : startdate.trim();
		enddate = enddate == null ? "" : enddate.trim();
		try {
			shopname = shopname == null ? "" : new String(shopname.trim().getBytes(
			"ISO-8859-1"), "UTF-8");
		} catch (UnsupportedEncodingException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		dayu = dayu == null ? "0" : dayu.trim();
		xiaoyu = xiaoyu == null ? "999999999999" : xiaoyu.trim();
		if (!shopname.equals("")) {
			sql += " and concat(MKL_COM,MKL_ID) like '%" + shopname + "%' ";
		}
		if (!startdate.equals("") && !enddate.equals("")) {
			amtsql += " and  (PEPDATE between '" + startdate + "' and '"
					+ enddate + "') ";
		}
		int c = 0;
		if (!dayu.equals("0") && !xiaoyu.equals("999999999999")) {
			amtsql += " having sum(succamt1-succamt2)/100 between '" + dayu
					+ "' and '" + xiaoyu + "' ";
			c = 1;
		}
		int start = 1;
		if (s != 0)
			start = s / 30 + 1;
		// System.out.println(sql);
		List tmp = service
				.findBySql("select mklcdno,sum(succamt1-succamt2)/100 as su from mkl_trade_card_day t "
						+ amtsql + " group by mklcdno");

		String mklc = "";
		HashMap map = new HashMap();
		for (int i = 0; i < tmp.size(); i++) {
			Object[] o = (Object[]) tmp.get(i);
			// System.out.println(o[0]+" "+o[1]);
			if (c == 1) {
				mklc += "'" + o[0] + "',";
			}
			map.put(o[0], o[1]);
		}
		if (!mklc.equals("")) {
			mklc += "''";
			sql += "and MKL_ID in (" + mklc + ")";
		}
		sql += " order by MKL_ID ";
		List userList = service.query("from MklInfo" + sql);
		List t = new ArrayList();
		short rowNum = 1;
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("new sheet");
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell = row.createCell((short) 0);


			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("商户编号");
			cell = row.createCell((short) 1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("商户名称");
			cell = row.createCell((short) 2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("销售品牌");
			cell = row.createCell((short) 3);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("地址");
			cell = row.createCell((short) 4);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("展位号");
			cell = row.createCell((short) 5);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("大类");
			cell = row.createCell((short) 6);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("小类");
			cell = row.createCell((short) 7);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("刷卡金额");

		for (int i = 0; i < userList.size(); i++) {
			MklInfo mm = (MklInfo) userList.get(i);
			if (map.get(mm.getMklId()) != null)
				mm.setSum(map.get(mm.getMklId()) + "");
			else
				mm.setSum("0.00");
				row = sheet.createRow(rowNum);
				cell = row.createCell((short) 0);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklId());
				cell = row.createCell((short) 1);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklCom());
				cell = row.createCell((short) 2);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklMark());
				cell = row.createCell((short) 3);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklAdd());
				cell = row.createCell((short) 4);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklShopid());
				cell = row.createCell((short) 5);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklClass());
				cell = row.createCell((short) 6);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getMklType());
				cell = row.createCell((short) 7);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(mm.getSum());
				rowNum++;

		}
			try {
				OutputStream out = this.getResponse().getOutputStream();
				this.getResponse().setHeader(
						"Content-disposition",
						"attachment; filename=" + startDate + "_" + endDate
								+ ".xls");
				this.getResponse().setContentType(
						"application/msexcel;charset=UTF-8");
				wb.write(out);
				out.flush();
				out.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		return null;
	}

	public InputStream excelToMkl() throws UnsupportedEncodingException {
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		LOG.info("导出MKL商户数据");
		String sql = "";
		if (m.getMpayTermid().indexOf("MKL") != -1) {
			sql += " where MKL_PRO in ('"
					+ m.getMpayTermid().replace(",", "','") + "') ";
		} else {
			sql += " where 1 =  1";
		}
		shopno = shopno == null ? "" : shopno.trim();
		shopname = shopname == null ? "" : new String(shopname.trim().getBytes(
				"ISO-8859-1"), "UTF-8");
		mklstate = mklstate == null ? "" : mklstate.trim();
		System.out.println("----" + shopname);
		if (!shopno.equals("")) {
			sql += " and MKL_ID like '%" + shopno + "%' ";
		}
		if (!shopname.equals("")) {
			sql += " and MKL_COM like '%" + shopname + "%' ";
		}
		if (!mklstate.equals("")) {
			sql += " and MKL_STATE = '" + mklstate + "' ";
		}
		sql += " order by MKL_ID ";
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List userList = service.query("from MklInfo" + sql);
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("new sheet");
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell = row.createCell((short) 0);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("商户编号");
		cell = row.createCell((short) 1);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("商户名称");
		cell = row.createCell((short) 2);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("销售品牌");
		cell = row.createCell((short) 3);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("地址");
		cell = row.createCell((short) 4);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("展位号");
		cell = row.createCell((short) 5);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("大类");
		cell = row.createCell((short) 6);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("小类");
		// cell = row.createCell((short) 7);
		// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		// cell.setCellValue("状态");
		// cell = row.createCell((short) 8);
		// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		// cell.setCellValue("撤场时间");
		// cell = row.createCell((short) 9);
		// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		// cell.setCellValue("撤场已结算时间");
		short rowNum = 1;
		for (int i = 0; i < userList.size(); i++) {
			row = sheet.createRow(rowNum);
			MklInfo o = (MklInfo) userList.get(i);
			cell = row.createCell((short) 0);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getMklId());
			cell = row.createCell((short) 1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getMklCom());
			cell = row.createCell((short) 2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getMklMark());
			cell = row.createCell((short) 3);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getMklAdd());
			cell = row.createCell((short) 4);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getMklShopid());
			cell = row.createCell((short) 5);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getMklClass());
			cell = row.createCell((short) 6);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getMklType());
			String state = o.getMklState();
			if (state.equals("1"))
				state = "正常开通";
			if (state.equals("9"))
				state = "撤场未结算";
			if (state.equals("0"))
				state = "撤场已结算";
			// cell = row.createCell((short) 7);
			// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			// cell.setCellValue(state);
			// cell = row.createCell((short) 8);
			// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			// if(o.getMklDelDate()!=null){
			// cell.setCellValue(formatter.format(o.getMklCrDate().getTime()));
			// }
			// cell = row.createCell((short) 9);
			// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			// if(o.getMklLtDate()!=null){
			// cell.setCellValue(formatter.format(o.getMklLtDate().getTime()));
			// }
			rowNum++;
		}
		try {
			OutputStream out = this.getResponse().getOutputStream();
			this.getResponse().setHeader("Content-disposition",
					"attachment; filename=data.xls");
			this.getResponse().setContentType(
					"application/msexcel;charset=UTF-8");
			wb.write(out);
			out.flush();
			out.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public InputStream process_dkqs() {
		LOG.info("下载代扣清算数据...");
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String sql = "";
		if (m.getMpayTermid().indexOf("MKL") != -1) {
			sql += " where mkl_pro in ('"
					+ m.getMpayTermid().replace(",", "','") + "') ";
		} else {
			sql += " where 1 = 1 ";
		}
		startDate = startDate == null ? "" : startDate.trim().replace("-", "");
		termid = termid == null ? "" : termid.trim();
		trdType = trdType == null ? "" : trdType.trim();
		if (!startDate.equals("")) {
			sql += " and (BEG_DP_DATE like '" + startDate + "%') ";
		}
		if (!termid.equals("")) {
			sql += " and DP_MER_SEQNO like '%" + termid + "%' ";
		}
		if (!trdType.equals("") && !trdType.equals("9")) {
			sql += " and STATE = '" + trdType + "' ";
		}
		System.out.println(sql);
		List userList = serviceStlmpay.query("from DeputyCashInfoView " + sql);
		List tmp = new ArrayList();
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("new sheet");
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell = row.createCell((short) 0);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("商户名称");
		cell = row.createCell((short) 1);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("品牌");
		cell = row.createCell((short) 2);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("卡号");
		cell = row.createCell((short) 3);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("代扣类型");
		cell = row.createCell((short) 4);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("代扣金额");
		cell = row.createCell((short) 5);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("已扣金额");
		cell = row.createCell((short) 6);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("状态");
		cell = row.createCell((short) 7);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("最后代扣日期");
		cell = row.createCell((short) 8);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("开始代扣日期");
		cell = row.createCell((short) 9);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("结束代扣日期");
		short rowNum = 1;
		for (int i = 0; i < userList.size(); i++) {
			row = sheet.createRow(rowNum);
			DeputyCashInfoView o = (DeputyCashInfoView) userList.get(i);
			cell = row.createCell((short) 0);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getMklCom());
			cell = row.createCell((short) 1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getMklMark());
			cell = row.createCell((short) 2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getMklId());
			cell = row.createCell((short) 3);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("租金、空调费、电费等");
			cell = row.createCell((short) 4);
			cell.setCellValue(Double.valueOf(Util
					.FormatSum(o.getDptyAmt() + "")));
			cell = row.createCell((short) 5);
			cell.setCellValue(Double.valueOf(Util
					.FormatSum(o.getDealAmt() + "")));
			cell = row.createCell((short) 6);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			if (o.getState().equals("1"))
				cell.setCellValue("已扣清");
			else
				cell.setCellValue("未扣清");
			cell = row.createCell((short) 7);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getCurDealDate());
			cell = row.createCell((short) 8);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getBegDpDate());
			cell = row.createCell((short) 9);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getEndDpDate());
			rowNum++;
		}
		try {
			OutputStream out = this.getResponse().getOutputStream();
			this.getResponse().setHeader("Content-disposition",
					"attachment; filename=" + startDate + ".xls");
			this.getResponse().setContentType(
					"application/msexcel;charset=UTF-8");
			wb.write(out);
			out.flush();
			out.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public InputStream process_qs() {
		LOG.info("下载清算数据...");
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String sql = " where 1 = 1 ";
		if (m.getIsAll() == 0) {
			String ss = (String) getRequest().getSession().getAttribute(
					"termgroup");
			if (ss.indexOf("C") != -1 || ss.indexOf("c") != -1) {
				sql += " and ( concat('C',trim(substr(TERMINAL_NO,5))) in ("
						+ ss + "))";
			} else if (m.getMpayTermid().indexOf("MKL") != -1) {
				sql += " and term_seqno in ('"
						+ m.getMpayTermid().replace(",", "','") + "') ";
			} else {
				sql += " and ( TERMINAL_NO in (" + ss + ")) ";
			}
		}
		Hashtable mklT = new Hashtable();
		List mkl = service.query("from MklInfo");
		for (int i = 0; i < mkl.size(); i++) {
			MklInfo mk = (MklInfo) mkl.get(i);
			mklT.put(mk.getMklId().trim(), mk);
		}
		Hashtable shop = new Hashtable();
		List shopl;
		if (m.getIsAll() == 0) {
			shopl = serviceMysql.query("from MpayOrg where shop_id = '"
					+ m.getShopId() + "' ");
			for (int i = 0; i < shopl.size(); i++) {
				MpayOrg mo = (MpayOrg) shopl.get(i);
				shop.put(mo.getTermid(), mo.getTermNick() == null ? "" : mo
						.getTermNick()
						+ " [" + mo.getTermName() + "]");
			}
		}

		startDate = startDate == null ? "" : startDate.trim().replace("-", "");
		endDate = endDate == null ? "" : endDate.trim().replace("-", "");
		termid = termid == null ? "" : termid.trim();
		trdType = trdType == null ? "" : trdType.trim();
		if (!startDate.equals("") && !endDate.equals("")) {
			sql += " and (PAY_DATE between '" + startDate.substring(0, 8)
					+ "' and '" + endDate.substring(0, 8) + "') ";
		}
		if (!termid.equals("")) {
			sql += " and TERMINAL_NO like '%" + termid + "%' ";
		}
		if (!trdType.equals("")) {
			sql += " and TRD_TYPE in ('" + trdType + "') ";
		}
		sql += " order by PAY_DATE desc";
		System.out.println(sql);

		List userList = serviceStlmpay
				.query("from OutMerchantTradeList " + sql);
		List tmp = new ArrayList();
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("new sheet");
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell = row.createCell((short) 0);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("商户名称");
		cell = row.createCell((short) 1);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("终端号/卡号");
		cell = row.createCell((short) 2);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("交易类型");
		cell = row.createCell((short) 3);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("交易金额");
		cell = row.createCell((short) 4);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("手续费");
		cell = row.createCell((short) 5);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("结算金额");
		cell = row.createCell((short) 6);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("结算日期");
		short rowNum = 1;
		for (int i = 0; i < userList.size(); i++) {
			row = sheet.createRow(rowNum);
			OutMerchantTradeList o = (OutMerchantTradeList) userList.get(i);
			if ("0123456789".indexOf(o.getTerminalNo().charAt(0)) == -1) {
				cell = row.createCell((short) 1);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(o.getTerminalNo().substring(4));
				cell = row.createCell((short) 0);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				MklInfo mk = (MklInfo) mklT.get(o.getTerminalNo().substring(4));
				if (mk != null) {
					cell.setCellValue(mk.getMklCom());
					cell = row.createCell((short) 7);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(mk.getMklMark());
					cell = row.createCell((short) 8);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(mk.getMklShopid());
					cell = row.createCell((short) 9);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(mk.getMklClass());
					cell = row.createCell((short) 10);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(mk.getMklType());
					cell = row.createCell((short) 11);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(mk.getMklAdd());
					cell = row.createCell((short) 12);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(mk.getMklAdd().substring(0,
							mk.getMklAdd().indexOf("楼")));
					row = sheet.createRow(0);
					cell = row.createCell((short) 7);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue("品牌");
					cell = row.createCell((short) 8);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue("商户编号");
					cell = row.createCell((short) 9);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue("大类");
					cell = row.createCell((short) 10);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue("小类");
					cell = row.createCell((short) 11);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue("地址");
					cell = row.createCell((short) 12);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue("楼层");
					row = sheet.createRow(rowNum);
				}
			} else {
				cell = row.createCell((short) 1);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(o.getTerminalNo());
				cell = row.createCell((short) 0);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				if (shop.get(o.getTerminalNo()) != null)
					cell.setCellValue(shop.get(o.getTerminalNo()) + "");
			}
			cell = row.createCell((short) 2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			if (o.getTrdType().equals("000"))
				cell.setCellValue("消费");
			else if (o.getTrdType().equals("700"))
				cell.setCellValue("销挂账");
			else if (o.getTrdType().equals("100"))
				cell.setCellValue("调账");
			else if (o.getTrdType().equals("X00"))
				cell.setCellValue("代扣");
			else
				cell.setCellValue(o.getTrdType());
			cell = row.createCell((short) 3);
			cell.setCellValue(Double.valueOf(Util.FormatSum(o.getTradeAmt()
					+ "")));
			cell = row.createCell((short) 4);
			cell.setCellValue(Double.valueOf(Util
					.FormatSum(o.getStlmFee() + "")));
			cell = row.createCell((short) 5);
			cell.setCellValue(Double.valueOf(Util
					.FormatSum(o.getStlmAmt() + "")));
			cell = row.createCell((short) 6);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(o.getPayDate());
			rowNum++;
		}
		try {
			OutputStream out = this.getResponse().getOutputStream();
			this.getResponse().setHeader(
					"Content-disposition",
					"attachment; filename=" + startDate + "_" + endDate
							+ ".xls");
			this.getResponse().setContentType(
					"application/msexcel;charset=UTF-8");
			wb.write(out);
			out.flush();
			out.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public InputStream getDownloadTrade() {
		LOG.info("下载交易数据...");

		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		Hashtable code = new Hashtable();
		Hashtable mercode = new Hashtable();
		Hashtable mertercode = new Hashtable();
		Hashtable termcode = new Hashtable();
		Hashtable mklT = new Hashtable();
		int cg = 0;// 成功交易笔数
		String cx = "";// 撤销交易代码
		String ysq = "";// 预授权
		Hashtable tableT = new Hashtable();
		List tradeT = serviceMysql.listAll("CodeView");
		for (int i = 0; i < tradeT.size(); i++) {
			CodeView mc = (CodeView) tradeT.get(i);
			tableT.put(mc.getId().getCodeid(), "[" + mc.getId().getBankName()
					+ "] " + mc.getId().getCodename());
			if (mc.getId().getCodename().indexOf("撤销") != -1
					|| mc.getId().getCodename().indexOf("退货") != -1) {
				cx += "'" + mc.getId().getCodeid() + "',";
			}
			if (mc.getId().getCodename().endsWith("预授权")
					|| mc.getId().getCodename().endsWith("预授权撤销")) {
				ysq += "'" + mc.getId().getCodeid() + "',";
			}
		}
		cx += "''";
		ysq += "''";
		List mkl = service.query("from MklInfo");
		for (int i = 0; i < mkl.size(); i++) {
			MklInfo mk = (MklInfo) mkl.get(i);
			mklT.put(mk.getMklId().trim(), mk);
		}
		List tradeFee = serviceMysql.listAll("MpayTradeFee");
		for (int i = 0; i < tradeFee.size(); i++) {
			MpayTradeFee fee = (MpayTradeFee) tradeFee.get(i);
			if (!fee.getMercode().equals("0") && !fee.getTercode().equals("0")) {
				mertercode.put(fee.getMercode() + "," + fee.getTercode(), fee);
			} else if (!fee.getMercode().equals("0")
					&& fee.getTercode().equals("0")
					&& fee.getTrancde().equals("0")) {
				mercode.put(fee.getMercode(), fee);
			} else if (!fee.getTrancde().equals("0")
					&& fee.getMercode().equals("0")
					&& fee.getTercode().equals("0")) {
				code.put(fee.getTrancde(), fee);
			} else if (!fee.getTrancde().equals("0")
					&& !fee.getMercode().equals("0")
					&& fee.getTercode().equals("0")) {
				termcode.put(fee.getMercode() + "," + fee.getTrancde(), fee);
			}
		}
		if (termid == null)
			termid = "";
		else {
			try {
				termid = new String(termid.getBytes("ISO-8859-1"), "UTF-8");
			} catch (UnsupportedEncodingException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		System.out.println(succ + "-[" + startDate + "][" + endDate + "]["
				+ termid + "]");
		termid = termid.replace("A", "%");
		String tablename = m.getTableName();
		if (type != null && type.equals("mkl")) {
			tablename = "Mkl_Trade_Info";
		}
		String tablename2 = tablename.replace("_", "");
		String sql = "";
		sql += " from "
				+ tablename2
				+ " where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%') and  (ORG_APRESPN is not null and ORG_APRESPN!='FF')  and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') ";
		if (!startDate.equals("") && !endDate.equals("")) {
			sql += " and (pepdate between '"
					+ startDate.split("T")[0].replace("-", "") + "' and '"
					+ endDate.split("T")[0].replace("-", "") + "') ";
		} else {
			sql += " and pepdate = '" + Util.getCurrentDate("yyyyMMdd") + "' ";
		}
		if (succ) {
			if (type != null && type.equals("mkl")) {
				sql += " and ( ORG_APRESPN = '00') ";
			} else {
				sql += " and (FST_SDRESPN = '00' or ORG_APRESPN = '00') ";
			}
		}
		if (!termid.trim().equals("") && !termid.trim().equals("undefined")) {
			if (type != null && type.equals("mkl")) {
				sql += " and concat(MKL_COM,MKL_ID,MKL_MARK) like '%"
						+ termid.trim() + "%' ";
			} else {
				sql += " and concat(termid,ORG_TERMCDE,ORG_MERCODE) like '%"
						+ termid.trim() + "%' ";
			}
		}
		if (card != null && !card.equals("")) {
			if (type != null && type.equals("mkl")) {
				sql += " and MKLCDNO like '%" + card + "%' ";
			}
		}
		if (m.getIsAll() == 0) {
			String ss = (String) getRequest().getSession().getAttribute(
					"termgroup");
			if (type.equals("mkl")) {
				if (ss.indexOf("C") != -1 || ss.indexOf("c") != -1) {
					sql += " and concat('C',trim(mklcdno)) in (" + ss + ")";
				} else {
					sql += " and ORG_TERMCDE in (" + ss + ") ";
				}
			} else {
				if (ss.indexOf("C") != -1 || ss.indexOf("c") != -1) {
					sql += " and ( concat('C',trim(OUTCDNO)) in (" + ss
							+ ") or concat('C',trim(substr(INTCDNO,5)))in ("
							+ ss + ") )";
				} else {
					sql += " and ( termid in (" + ss + ") or ORG_TERMCDE in ("
							+ ss + ") or  ORG_MERCODE in (" + ss + "))";
				}
			}
		}
		String sqlsum = "select  sum(case  when trancde in (" + ysq
				+ ") then '0'  when trancde in (" + cx
				+ ") then concat('-',tranamt)  else tranamt end ) "
				+ sql.replace(tablename2, tablename)
				+ " and (FST_SDRESPN = '00' or ORG_APRESPN = '00') ";
		if (type != null && type.equals("mkl")) {
			sqlsum = "select  sum(case  when trancde in (" + ysq
					+ ") then '0'  when trancde in (" + cx
					+ ") then concat('-',tranamt) else tranamt end ) "
					+ sql.replace(tablename2, tablename)
					+ " and (ORG_APRESPN = '00') ";
		}
		Connection c = service.getConnection();
		String sum = "0";
		try {
			PreparedStatement ps = c.prepareStatement(sqlsum);
			ResultSet rset = ps.executeQuery();
			rset.next();
			sum = rset.getString(1);
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(sum);
		sql += " order by pepdate desc,peptime desc ";

		Hashtable ydmT = new Hashtable();
		List ydm = serviceMysql.listAll("Mpayrespcode");
		for (int i = 0; i < ydm.size(); i++) {
			Mpayrespcode mp = (Mpayrespcode) ydm.get(i);
			ydmT.put(mp.getRespCode(), mp.getRespCode() + "["
					+ mp.getRespName() + "]");
		}
		Hashtable shop = new Hashtable();
		List shopl;
		if (m.getIsAll() == 0) {
			shopl = serviceMysql.query("from MpayOrg where shop_id = '"
					+ m.getShopId() + "' ");
		} else {
			shopl = serviceMysql.listAll("MpayOrg");
		}
		for (int i = 0; i < shopl.size(); i++) {
			MpayOrg mo = (MpayOrg) shopl.get(i);
			shop.put(mo.getTermid(), mo.getTermNick() == null ? "" : mo
					.getTermNick()
					+ " [" + mo.getTermName() + "]");
		}
		System.out.println(sql);
		List tmp = service.query(sql);
		List t = new ArrayList<Trade>();
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("new sheet");
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell = row.createCell((short) 0);
		if (type.equals("mkl")) {
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("品牌名称");
			cell = row.createCell((short) 1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("交易金额");
			cell = row.createCell((short) 2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("交易时间");
			cell = row.createCell((short) 3);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("交易类型");
			cell = row.createCell((short) 4);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("应答码");
			cell = row.createCell((short) 5);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("楼层");
			cell = row.createCell((short) 6);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("经营大类");
			cell = row.createCell((short) 7);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("门牌号");
			cell = row.createCell((short) 8);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("厂商编号");
			cell = row.createCell((short) 9);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("商户名称");
			cell = row.createCell((short) 10);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("经营小类");
			cell = row.createCell((short) 11);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("转出卡号");
			cell = row.createCell((short) 12);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("转入卡号");
			cell = row.createCell((short) 13);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("终端号码");
			cell = row.createCell((short) 14);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("参考手续费");
			cell = row.createCell((short) 15);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("流水号");
			cell = row.createCell((short) 16);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("检索参考号");
		} else {
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("交易时间");
			cell = row.createCell((short) 1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("交易类型");
			cell = row.createCell((short) 2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("转出卡号");
			cell = row.createCell((short) 3);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("转入卡号");
			cell = row.createCell((short) 4);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("交易金额");
			cell = row.createCell((short) 5);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("参考手续费");
			cell = row.createCell((short) 6);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("参考入账金额");
			cell = row.createCell((short) 7);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("应答码");
			cell = row.createCell((short) 8);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("二次应答码");
			cell = row.createCell((short) 9);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("终端号码");
			cell = row.createCell((short) 10);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("商户名称");
			cell = row.createCell((short) 11);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("流水号");
			cell = row.createCell((short) 12);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("检索参考号");
			cell = row.createCell((short) 13);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("电话号码");
		}
		Iterator itr = tmp.iterator();
		WtPepJnls w;
		short rowNum = 1;
		int hjsxf = 0;
		int hjrzje = 0;
		while (itr.hasNext()) {
			// System.out.println(rowNum);
			row = sheet.createRow(rowNum);
			if (tablename.equals("Wt_Pep_Jnls")) {
				w = (WtPepJnls) itr.next();
			} else if (tablename.equals("Mkl_Trade_Info")) {
				w = new WtPepJnls((MklTradeInfo) itr.next());
			} else {
				w = new WtPepJnls((WtPepJnlsBak) itr.next());
			}
			String d = w.getId().getPepdate();
			String tt = w.getPeptime();
			String tradeTime = d.substring(0, 4) + "-" + d.substring(4, 6)
					+ "-" + d.substring(6, 8) + " " + tt.substring(0, 2) + ":"
					+ tt.substring(2, 4) + ":" + tt.substring(4, 6);
			String tradeType = "";
			if (w.getTrancde() != null)
				tradeType = tableT.get(w.getTrancde()) != null ? tableT.get(w
						.getTrancde())
						+ "" : w.getTrancde();

			String outCard = Util.toInCard(w.getOutcdno());
			// System.out.println(m.getMpayUserid()+"---------");
			if (m.getMpayUserid().equals("BJJMJ")) {
				outCard = w.getOutcdno();
			}
			String inCard = "";
			if (w.getIntcdno() != null && w.getIntcdno().startsWith("MKL")) {
				String in = w.getIntcdno().substring(4).trim();
				System.out.println(in);
				if (mklT.get(in) != null) {
					MklInfo mk = (MklInfo) mklT.get(in);
					inCard = mk.getMklId() + "/" + mk.getMklMark() + "/"
							+ mk.getMklType() + "/" + mk.getMklAdd() + "/"
							+ mk.getMklCom();
					cell = row.createCell((short) 13);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(mk.getMklType());
					cell = row.createCell((short) 14);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(mk.getMklShopid());
					cell = row.createCell((short) 15);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(mk.getMklMark());
					cell = row.createCell((short) 16);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(mk.getMklCom());
				} else {
					inCard = in;
				}
			} else if (w.getIntcdno() != null
					&& w.getIntcdno().indexOf("|") != -1) {
				String msg = w.getIntcdno().trim() + "|" + w.getTermpro();
				String ss[] = msg.split("\\|");
				inCard = ss[1];
				cell = row.createCell((short) 14);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(ss[0]);
				cell = row.createCell((short) 15);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(ss[2]);
			} else {
				inCard = Util.formatString(w.getIntcdno());
			}

			String amt = "";
			if (cx.indexOf(w.getTrancde().trim()) != -1) {
				amt = "-" + Util.FormatSum(w.getTranamt());
			} else {
				amt = Util.FormatSum(w.getTranamt());
			}
			String tradeYdm = "";
			String sxf = "";
			String rzje = "";
			String str = w.getFstSdrespn();
			if (str == null)
				str = w.getOrgAprespn();
			if (str == null)
				str = "";
			str = str.trim();
			if (ysq.indexOf(w.getTrancde().trim()) == -1 && str.equals("00")) {
				cg++;
				// 计算手续费
				if (termcode.get(w.getTermid().trim() + ","
						+ w.getTrancde().trim()) != null)
					sxf = (getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) termcode.get(w.getTermid().trim()
									+ "," + w.getTrancde().trim())));
				else if (mertercode.get(w.getId().getOrgMercode().trim() + ","
						+ w.getId().getOrgTermcde()) != null)
					sxf = getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) mertercode.get(w.getId()
									.getOrgMercode().trim()
									+ "," + w.getId().getOrgTermcde()));
				else if (mertercode.get(w.getId().getOrgMercode().trim() + ","
						+ w.getTrancde().trim()) != null)
					sxf = getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) mertercode.get(w.getId()
									.getOrgMercode().trim()
									+ "," + w.getTrancde().trim()));
				else if (mercode.get(w.getId().getOrgMercode().trim()) != null)
					sxf = getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) mercode.get(w.getId()
									.getOrgMercode().trim()));
				else if (termcode.get(w.getId().getOrgTermcde().trim() + ","
						+ w.getTrancde().trim()) != null)
					sxf = getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) termcode.get(w.getId()
									.getOrgTermcde().trim()
									+ "," + w.getTrancde().trim()));
				else if (code.get(w.getTrancde().trim()) != null)
					sxf = getTradefee(Util.FormatSum(w.getTranamt()),
							(MpayTradeFee) code.get(w.getTrancde().trim()));

				else
					sxf = "-";
			}
			if (cx.indexOf(w.getTrancde().trim()) != -1 && !sxf.equals("-")) {
				sxf = "-" + sxf;
			}
			if (!sxf.equals("-")) {
				hjsxf += Integer.valueOf(Util.moveRightTwo(sxf));
				rzje = Long.valueOf(w.getTranamt().trim())
						- Long.valueOf(Util.moveRightTwo(sxf)) + "";
			} else {
				if (cx.indexOf(w.getTrancde().trim()) != -1)
					rzje = "-" + w.getTranamt().trim();
				else
					rzje = w.getTranamt().trim();
			}
			if (!str.equals("00") || tradeType.indexOf("河南") != -1
					|| ysq.indexOf(w.getTrancde().trim()) != -1) {
				rzje = "-";
			}

			if (!rzje.equals("-")) {
				hjrzje += Long.valueOf(rzje);
			}
			tradeYdm = ydmT.get(str) != null ? (String) ydmT.get(str) : str;
			String tradeTermid = w.getTermid();
			String nxydm = w.getNxtSdrespn() == null ? "" : w.getNxtSdrespn();
			str = w.getTermid() == null ? "" : w.getTermid().trim();
			String tradeShop = "";
			tradeShop = shop.get(str) != null ? (String) shop.get(str) : shop
					.get(w.getId().getOrgTermcde()) != null ? (String) shop
					.get(w.getId().getOrgTermcde()) : "-";
			if (shop.get(w.getId().getOrgTermcde()) != null)
				tradeTermid = w.getId().getOrgTermcde();
			String tradeTrace = w.getId().getTrace() + "";
			String tradeSeq = w.getSyseqno();
			if (type.equals("mkl")) {
				cell = row.createCell((short) 0);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(w.getTermtrc());
				cell = row.createCell((short) 1);
				cell.setCellValue(Util.FormatSum(w.getTranamt()));
				cell = row.createCell((short) 2);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(tradeTime);
				cell = row.createCell((short) 3);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(tradeType);
				cell = row.createCell((short) 4);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(tradeYdm);
				cell = row.createCell((short) 5);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(w.getSendcde());
				cell = row.createCell((short) 6);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(w.getTrack3());
				cell = row.createCell((short) 7);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(w.getTrntime());
				cell = row.createCell((short) 8);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(w.getTrack2());
				cell = row.createCell((short) 9);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(w.getTrndate());
				cell = row.createCell((short) 10);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(w.getPoitcde());
				cell = row.createCell((short) 11);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(outCard);
				cell = row.createCell((short) 12);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(inCard);
				cell = row.createCell((short) 13);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(w.getId().getOrgTermcde());
				cell = row.createCell((short) 14);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(sxf);
				cell = row.createCell((short) 15);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(w.getId().getTrace());
				cell = row.createCell((short) 16);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(w.getSyseqno());
			} else {
				cell = row.createCell((short) 0);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(tradeTime);
				cell = row.createCell((short) 1);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(tradeType);
				cell = row.createCell((short) 2);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(outCard);
				cell = row.createCell((short) 3);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(inCard);
				cell = row.createCell((short) 4);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(amt);
				cell = row.createCell((short) 5);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				if (w.getBit28() != null && !w.getBit28().startsWith("D")) {
					cell.setCellValue(w.getBit28());
				} else {
					cell.setCellValue(sxf);
				}
				cell = row.createCell((short) 6);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(Util.FormatSum(rzje));
				cell = row.createCell((short) 7);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(tradeYdm);
				cell = row.createCell((short) 8);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(nxydm);
				cell = row.createCell((short) 9);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(tradeTermid);
				cell = row.createCell((short) 10);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(tradeShop);
				cell = row.createCell((short) 11);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(tradeTrace);
				cell = row.createCell((short) 12);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(tradeSeq);
				cell = row.createCell((short) 13);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(w.getTermid());
				if (w.getIntcdno() != null && !w.getIntcdno().startsWith("-")
						&& w.getIntcdno().indexOf("#") != -1) {
					// trade.setOutcard(w.getIntcdno());
					// trade.setType(trade.getType().replace("消费", "资金归集"));
					String shopid = "";
					String shoptime = "";
					String shopflag = "";
					try {
						PreparedStatement ps = c
								.prepareStatement("select t.t_cust_number,to_char(t.ordered_date,'yyyy-mm-dd hh24:mm:ss'),decode(t.processed_flag,1,'已完成','未完成') from bank_intf_orders t where bill_number = '"
										+ w.getIntcdno().trim() + "'");
						ResultSet rset = ps.executeQuery();
						rset.next();
						shopid = rset.getString(1);
						shoptime = rset.getString(2);
						shopflag = rset.getString(3);
						ps.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					cell = row.createCell((short) 14);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(shopid);
					cell = row.createCell((short) 15);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(shopflag);
					cell = row.createCell((short) 16);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(shoptime);
					row = sheet.createRow((short) 0);
					cell = row.createCell((short) 14);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue("单号所属商户");
					row = sheet.createRow((short) 0);
					cell = row.createCell((short) 15);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue("单号状态");
					row = sheet.createRow((short) 0);
					cell = row.createCell((short) 16);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue("生成时间");
				}
			}
			rowNum++;
		}
		row = sheet.createRow(rowNum);
		cell = row.createCell((short) 0);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("总交易额:");
		cell = row.createCell((short) 1);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(Util.FormatSum(sum));
		rowNum++;
		row = sheet.createRow(rowNum);
		cell = row.createCell((short) 0);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("成功交易笔数:");
		cell = row.createCell((short) 1);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(cg + "");
		rowNum++;
		row = sheet.createRow(rowNum);
		cell = row.createCell((short) 0);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("成功交易参考手续费:");
		cell = row.createCell((short) 1);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(Util.FormatSum(hjsxf + ""));
		rowNum++;
		if (type.equals("mkl")) {

		} else {
			row = sheet.createRow(rowNum);
			cell = row.createCell((short) 0);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("成功交易参考入账金额:");
			cell = row.createCell((short) 1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(Util.FormatSum(hjrzje + ""));
		}// System.out.println("+++++++++++++"+tmp.size());
		try {
			c.close();
			OutputStream out = this.getResponse().getOutputStream();
			this.getResponse().setHeader("Content-disposition",
					"attachment; filename=data.xls");
			this.getResponse().setContentType(
					"application/msexcel;charset=UTF-8");
			wb.write(out);
			out.flush();
			out.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public List<Chars> getDayTrade(String date) {
		String cx = "";// 撤销交易代码
		Hashtable tableT = new Hashtable();
		List tradeT = serviceMysql.listAll("CodeView");
		for (int i = 0; i < tradeT.size(); i++) {
			CodeView mc = (CodeView) tradeT.get(i);
			tableT.put(mc.getId().getCodeid(), "[" + mc.getId().getBankName()
					+ "] " + mc.getId().getCodename());
			if (mc.getId().getCodename().indexOf("撤销") != -1
					|| mc.getId().getCodename().indexOf("退货") != -1) {
				cx += "'" + mc.getId().getCodeid() + "',";
			}
		}
		cx += "''";
		String sql1 = "";
		String avg = "";
		DateFormat format = new SimpleDateFormat("HH");
		DateFormat format2 = new SimpleDateFormat("HHmmss");
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String tablename = m.getTableName();
		String tablename2 = tablename.replace("_", "");
		Hashtable ta = new Hashtable();
		if (m.getIsAll() == 0) {
			String ss = (String) getRequest().getSession().getAttribute(
					"termgroup");
			if (ss.indexOf("C") != -1)
				ss = ss.replace("C", "");
			sql1 = " and ( termid in (" + ss + ") or ORG_TERMCDE in (" + ss
					+ ") or  OUTCDNO in (" + ss
					+ ") or substr(INTCDNO,5) in  (" + ss + "))";
			avg = " and ( termid in (" + ss + ") or ORG_TERMCDE in (" + ss
					+ ") or  OUTCDNO in (" + ss
					+ ") or substr(INTCDNO,5) in  (" + ss + "))";
		}
		List chars_tmp = new ArrayList<Chars>();
		String sql = "";
		avg = "select FLOOR(nvl(AVG(t.tranamt)/100,0)) from "
				+ tablename
				+ " t where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%') and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ avg
				+ " and (FST_SDRESPN = '00' or ORG_APRESPN = '00')  and (pepdate = '"
				+ date + "')";
		sql += "select substr(peptime,0,2),count(*),sum(case when trancde in ("
				+ cx
				+ ") then concat('-',tranamt) else tranamt end )/100 from "
				+ tablename
				+ " t where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%') and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ sql1
				+ " and (FST_SDRESPN = '00' or ORG_APRESPN = '00')  and (pepdate = '"
				+ date + "') group by substr(peptime,0,2)";
		Connection c = service.getConnection();
		try {
			LOG.info(sql);
			PreparedStatement ps = c.prepareStatement(sql);
			ResultSet rset = ps.executeQuery();
			long cou = 0;
			long su = 0;
			long pr = 0;
			while (rset.next()) {
				cou += Long.valueOf(rset.getString(2));
				if (rset.getString(3).indexOf(".") != -1
						&& !rset.getString(3).equals("")) {
					if (!rset.getString(3).startsWith("."))
						su += Long.valueOf(rset.getString(3).substring(0,
								rset.getString(3).indexOf(".")));
				} else
					su += Long.valueOf(rset.getString(3));
				ta.put(rset.getString(1), rset.getString(3) + ","
						+ rset.getString(2));
			}
			c.close();
			if (su != 0) {
				pr = su / cou / 2;
			}

			Date i = format2.parse("000000");
			while (true) {
				String d = format.format(i);
				Chars cc = new Chars();
				if (ta.get(d) != null) {
					String data = (String) ta.get(d);
					cc.setName(d + "时");
					cc.setViews(data.split(",")[0]);
					cc.setVisits(Integer.valueOf(data.split(",")[1]) * pr + "");
				} else {
					cc.setName(d + "时");
					cc.setViews("0");
					cc.setVisits("0");
				}
				cc.setPre(pr + "");
				chars_tmp.add(cc);
				if (date.equals(Util.getCurrentDate("yyyyMMdd"))) {
					if (format.format(i).equals(Util.getCurrentDate("HH"))) {
						break;
					}
				}
				if (format.format(i).equals("23"))
					break;
				Calendar theCa = Calendar.getInstance();
				theCa.setTime(i);
				theCa.add(theCa.HOUR, 1);
				i = theCa.getTime();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				c.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return chars_tmp;

	}

	public List<Chars> getMouthTrade(String start, String end) {
		String sql1 = "";
		String avg = "";
		String cx = "";// 撤销交易代码
		Hashtable tableT = new Hashtable();
		List tradeT = serviceMysql.listAll("CodeView");
		for (int i = 0; i < tradeT.size(); i++) {
			CodeView mc = (CodeView) tradeT.get(i);
			tableT.put(mc.getId().getCodeid(), "[" + mc.getId().getBankName()
					+ "] " + mc.getId().getCodename());
			if (mc.getId().getCodename().indexOf("撤销") != -1
					|| mc.getId().getCodename().indexOf("退货") != -1) {
				cx += "'" + mc.getId().getCodeid() + "',";
			}
		}
		cx += "''";
		DateFormat format = new SimpleDateFormat("yyyyMMdd");
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String tablename = m.getTableName();
		String tablename2 = tablename.replace("_", "");
		Hashtable ta = new Hashtable();
		if (m.getIsAll() == 0) {
			String ss = (String) getRequest().getSession().getAttribute(
					"termgroup");
			if (ss.indexOf("C") != -1)
				ss = ss.replace("C", "");
			sql1 = " and ( termid in (" + ss + ") or ORG_TERMCDE in (" + ss
					+ ") or  OUTCDNO in (" + ss
					+ ") or substr(INTCDNO,5) in  (" + ss + "))";
			avg = " and ( termid in (" + ss + ") or ORG_TERMCDE in (" + ss
					+ ") or  OUTCDNO in (" + ss
					+ ") or substr(INTCDNO,5) in  (" + ss + "))";
		}
		List chars_tmp = new ArrayList<Chars>();

		String sql = "";
		avg = "select FLOOR(nvl(AVG(t.tranamt)/100,0)) from "
				+ tablename
				+ " t where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%')and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ avg
				+ " and (FST_SDRESPN = '00' or ORG_APRESPN = '00')  and (pepdate between '"
				+ start + "' and '" + end + "') ";
		sql += "select pepdate,count(*),sum(case when trancde in ("
				+ cx
				+ ") then concat('-',tranamt) else tranamt end )/100 from "
				+ tablename
				+ " t where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%') and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ sql1
				+ " and (FST_SDRESPN = '00' or ORG_APRESPN = '00')  and (pepdate between '"
				+ start + "' and '" + end + "') group by pepdate";
		Connection c = service.getConnection();
		try {
			LOG.info(sql);
			PreparedStatement ps = c.prepareStatement(sql);
			ResultSet rset = ps.executeQuery();
			long cou = 0;
			long su = 0;
			long pr = 0;
			while (rset.next()) {
				cou += Long.valueOf(rset.getString(2));
				if (rset.getString(3).indexOf(".") != -1
						&& !rset.getString(3).equals("")) {
					if (!rset.getString(3).startsWith("."))
						su += Long.valueOf(rset.getString(3).substring(0,
								rset.getString(3).indexOf(".")));
				} else
					su += Long.valueOf(rset.getString(3));
				ta.put(rset.getString(1).substring(6), rset.getString(3) + ","
						+ rset.getString(2));
			}
			if (su != 0) {
				pr = su / cou / 2;
			}

			Date i = format.parse(start);
			while (true) {
				String d = format.format(i).substring(6);
				Chars cc = new Chars();
				if (ta.get(d) != null) {
					String data = (String) ta.get(d);
					cc.setName(d + "日");
					// System.out.println(data);
					cc.setViews(data.split(",")[0]);
					cc.setVisits(Integer.valueOf(data.split(",")[1]) * pr + "");
				} else {
					cc.setName(d + "日");
					cc.setViews("0");
					cc.setVisits("0");
				}
				cc.setPre(pr + "");
				chars_tmp.add(cc);
				if (format.format(i).equals(Util.getCurrentDate("yyyyMMdd"))) {
					break;
				}
				if (format.format(i).equals(end))
					break;
				if (format.format(i).equals(
						end.substring(0, 6)
								+ Util.addZero(Util.getDayByMouth(end
										.substring(0, 6))
										+ "", 2)))
					break;
				Calendar theCa = Calendar.getInstance();
				theCa.setTime(i);
				theCa.add(theCa.DATE, 1);
				i = theCa.getTime();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				c.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return chars_tmp;
	}

	public List<Chars> getMouthTrade(String start, String end, String termid) {
		String sql1 = "";
		String avg = "";
		String cx = "";// 撤销交易代码
		Hashtable tableT = new Hashtable();
		List tradeT = serviceMysql.listAll("CodeView");
		for (int i = 0; i < tradeT.size(); i++) {
			CodeView mc = (CodeView) tradeT.get(i);
			tableT.put(mc.getId().getCodeid(), "[" + mc.getId().getBankName()
					+ "] " + mc.getId().getCodename());
			if (mc.getId().getCodename().indexOf("撤销") != -1
					|| mc.getId().getCodename().indexOf("退货") != -1) {
				cx += "'" + mc.getId().getCodeid() + "',";
			}
		}
		cx += "''";
		DateFormat format = new SimpleDateFormat("yyyyMMdd");
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String tablename = m.getTableName();
		String tablename2 = tablename.replace("_", "");
		Hashtable ta = new Hashtable();
		sql1 = " and termid in ('" + termid + "') ";
		avg = " and termid in ('" + termid + "') ";
		List chars_tmp = new ArrayList<Chars>();

		String sql = "";
		avg = "select FLOOR(nvl(AVG(t.tranamt)/100,0)) from "
				+ tablename
				+ " t where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%') and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ avg
				+ " and (FST_SDRESPN = '00' or ORG_APRESPN = '00')  and (pepdate between '"
				+ start + "' and '" + end + "') ";
		sql += "select pepdate,count(*),sum(case when trancde in ("
				+ cx
				+ ") then concat('-',tranamt) else tranamt end )/100 from "
				+ tablename
				+ " t where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%') and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ sql1
				+ " and (FST_SDRESPN = '00' or ORG_APRESPN = '00')  and (pepdate between '"
				+ start + "' and '" + end + "') group by pepdate";
		Connection c = service.getConnection();
		try {
			LOG.info(sql);
			PreparedStatement ps = c.prepareStatement(sql);
			ResultSet rset = ps.executeQuery();
			long cou = 0;
			long su = 0;
			long pr = 0;
			while (rset.next()) {
				cou += Long.valueOf(rset.getString(2));
				if (rset.getString(3).indexOf(".") != -1
						&& !rset.getString(3).equals("")) {
					if (!rset.getString(3).startsWith("."))
						su += Long.valueOf(rset.getString(3).substring(0,
								rset.getString(3).indexOf(".")));
				} else
					su += Long.valueOf(rset.getString(3));
				ta.put(rset.getString(1).substring(6), rset.getString(3) + ","
						+ rset.getString(2));
			}
			if (su != 0) {
				pr = su / cou / 2;
			}

			Date i = format.parse(start);
			while (true) {
				String d = format.format(i).substring(6);
				Chars cc = new Chars();
				if (ta.get(d) != null) {
					String data = (String) ta.get(d);
					cc.setName(d + "日");
					// System.out.println(data);
					cc.setViews(data.split(",")[0]);
					cc.setVisits(Integer.valueOf(data.split(",")[1]) * pr + "");
				} else {
					cc.setName(d + "日");
					cc.setViews("0");
					cc.setVisits("0");
				}
				cc.setPre(pr + "");
				chars_tmp.add(cc);
				if (format.format(i).equals(Util.getCurrentDate("yyyyMMdd"))) {
					break;
				}
				if (format.format(i).equals(end))
					break;
				if (format.format(i).equals(
						end.substring(0, 6)
								+ Util.addZero(Util.getDayByMouth(end
										.substring(0, 6))
										+ "", 2)))
					break;
				Calendar theCa = Calendar.getInstance();
				theCa.setTime(i);
				theCa.add(theCa.DATE, 1);
				i = theCa.getTime();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				c.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return chars_tmp;
	}

	public List<Chars> getYearTrade(String start, String end) {
		String sql1 = "";
		String avg = "";
		String cx = "";// 撤销交易代码
		Hashtable tableT = new Hashtable();
		List tradeT = serviceMysql.listAll("CodeView");
		for (int i = 0; i < tradeT.size(); i++) {
			CodeView mc = (CodeView) tradeT.get(i);
			tableT.put(mc.getId().getCodeid(), "[" + mc.getId().getBankName()
					+ "] " + mc.getId().getCodename());
			if (mc.getId().getCodename().indexOf("撤销") != -1
					|| mc.getId().getCodename().indexOf("退货") != -1) {
				cx += "'" + mc.getId().getCodeid() + "',";
			}
		}
		cx += "''";
		DateFormat format = new SimpleDateFormat("yyyyMMdd");
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String tablename = m.getTableName();
		if (start.startsWith("2009") && end.startsWith("2009")) {
			tablename = "wt_pep_jnls_bak_2009";
		}
		String tablename2 = tablename.replace("_", "");
		Hashtable ta = new Hashtable();
		if (m.getIsAll() == 0) {
			String ss = (String) getRequest().getSession().getAttribute(
					"termgroup");

			sql1 = " and ( termid in (" + ss + ") or ORG_TERMCDE in (" + ss
					+ ") or  OUTCDNO in (" + ss
					+ ") or substr(INTCDNO,5) in  (" + ss + "))";
			avg = " and ( termid in (" + ss + ") or ORG_TERMCDE in (" + ss
					+ ") or  OUTCDNO in (" + ss
					+ ") or substr(INTCDNO,5) in  (" + ss + "))";
		}
		List chars_tmp = new ArrayList<Chars>();
		String sql = "";
		avg = "select FLOOR(AVG(t.tranamt)/100) from "
				+ tablename
				+ " t where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%') and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ avg
				+ " and (FST_SDRESPN = '00' or ORG_APRESPN = '00')  and (pepdate between '"
				+ start.substring(0, 6) + "01' and '" + end.substring(0, 6)
				+ "31') ";
		sql += "select substr(pepdate,0,6),count(*),sum(case when trancde in ("
				+ cx
				+ ") then concat('-',tranamt) else tranamt end )/100 from "
				+ tablename
				+ " t where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%') and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ sql1
				+ " and (FST_SDRESPN = '00' or ORG_APRESPN = '00')  and (pepdate between '"
				+ start.substring(0, 6) + "01' and '" + end.substring(0, 6)
				+ "31') group by substr(pepdate,0,6)";
		Connection c = service.getConnection();
		try {
			// LOG.info(avg);
			// PreparedStatement ps = c.prepareStatement(avg);
			// ResultSet rset = ps.executeQuery();
			// rset.next();
			// String str = rset.getString(1);
			// str = Integer.valueOf(str) / 2 + "";
			// sql = sql.replace("ABC", "10000");
			LOG.info(sql);
			PreparedStatement ps = c.prepareStatement(sql);
			ResultSet rset = ps.executeQuery();
			long cou = 0;
			long su = 0;
			long pr = 0;
			while (rset.next()) {
				cou += Long.valueOf(rset.getString(2));
				if (rset.getString(3).indexOf(".") != -1
						&& !rset.getString(3).equals("")) {
					if (!rset.getString(3).startsWith("."))
						su += Long.valueOf(rset.getString(3).substring(0,
								rset.getString(3).indexOf(".")));
				} else
					su += Long.valueOf(rset.getString(3));
				ta.put(rset.getString(1), rset.getString(3) + ","
						+ rset.getString(2));
			}
			if (su != 0) {
				pr = su / cou / 2;
			}
			Date i = format.parse(start);
			while (true) {
				String d = format.format(i).substring(0, 6);
				Chars cc = new Chars();
				if (ta.get(d) != null) {
					String data = (String) ta.get(d);
					cc.setName(d + "月");
					cc.setViews(data.split(",")[0]);
					cc.setVisits(Integer.valueOf(data.split(",")[1]) * pr + "");
				} else {
					cc.setName(d + "月");
					cc.setViews("0");
					cc.setVisits("0");
				}
				cc.setPre(pr + "");
				chars_tmp.add(cc);
				if (format.format(i).substring(0, 6).equals(
						Util.getCurrentDate("yyyyMM"))) {
					break;
				}
				if (format.format(i).substring(0, 6)
						.equals(end.substring(0, 6)))
					break;
				Calendar theCa = Calendar.getInstance();
				theCa.setTime(i);
				theCa.add(theCa.MONTH, 1);
				i = theCa.getTime();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				c.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return chars_tmp;
	}

	public List<Chars> getYonY(String start, String end) {
		String sql1 = "";
		String avg = "";
		String cx = "";// 撤销交易代码
		Hashtable tableT = new Hashtable();
		List tradeT = serviceMysql.listAll("CodeView");
		for (int i = 0; i < tradeT.size(); i++) {
			CodeView mc = (CodeView) tradeT.get(i);
			tableT.put(mc.getId().getCodeid(), "[" + mc.getId().getBankName()
					+ "] " + mc.getId().getCodename());
			if (mc.getId().getCodename().indexOf("撤销") != -1
					|| mc.getId().getCodename().indexOf("退货") != -1) {
				cx += "'" + mc.getId().getCodeid() + "',";
			}
		}
		cx += "''";
		DateFormat format = new SimpleDateFormat("yyyyMMdd");
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String tablename = m.getTableName();
		String tablename2 = tablename.replace("_", "");
		Hashtable ta = new Hashtable();
		Hashtable old = new Hashtable();
		if (m.getIsAll() == 0) {
			String ss = (String) getRequest().getSession().getAttribute(
					"termgroup");
			if (ss.indexOf("C") != -1)
				ss = ss.replace("C", "");
			sql1 = " and ( termid in (" + ss + ") or ORG_TERMCDE in (" + ss
					+ ") or  OUTCDNO in (" + ss
					+ ") or substr(INTCDNO,5) in  (" + ss + "))";
			avg = " and ( termid in (" + ss + ") or ORG_TERMCDE in (" + ss
					+ ") or  OUTCDNO in (" + ss
					+ ") or substr(INTCDNO,5) in  (" + ss + "))";
		}
		List chars_tmp = new ArrayList<Chars>();

		String sql = "";
		sql += "select pepdate,sum(case when trancde in ("
				+ cx
				+ ") then concat('-',tranamt) else tranamt end )/100 from "
				+ tablename
				+ " t where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%')and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ sql1
				+ " and (FST_SDRESPN = '00' or ORG_APRESPN = '00')  and (pepdate between '"
				+ start + "' and '" + end + "') group by pepdate";
		Connection c = service.getConnection();
		try {
			LOG.info(sql);
			PreparedStatement ps = c.prepareStatement(sql);
			ResultSet rset = ps.executeQuery();
			while (rset.next()) {
				ta.put(rset.getString(1).substring(6), rset.getString(2));
			}
			String s = start.substring(0, 4);
			sql = sql.replace(s, Integer.valueOf(s) - 1 + "");
			sql = sql.replace("Wt_Pep_Jnls_Bak", "Wt_Pep_Jnls_Bak_2009");
			LOG.info(sql);
			ps = c.prepareStatement(sql);
			rset = ps.executeQuery();
			while (rset.next()) {
				old.put(rset.getString(1).substring(6), rset.getString(2));
			}
			Date i = format.parse(start);
			while (true) {
				String d = format.format(i).substring(6);
				Chars cc = new Chars();
				String o = (String) old.get(d);
				String n = (String) ta.get(d);
				cc.setName(d + "日");
				if (o != null)
					cc.setViews(o);
				else
					cc.setViews("0");
				if (n != null)
					cc.setVisits(n);
				else
					cc.setVisits("0");
				cc.setLevel("0");
				if (o != null && n != null && level != null
						&& !level.equals("0")) {
					double oo = Double.valueOf(o);
					double nn = Double.valueOf(n);
					double lev = (nn - oo) / oo * 100;

					if (lev < 0) {
						lev = Math.abs(lev);
						if (Double.valueOf(level) < lev) {
							cc.setLevel(n);
						} else
							cc.setLevel("0");
					}
				}
				cc.setPre(start.substring(0, 4));
				chars_tmp.add(cc);
				if (format.format(i).equals(Util.getCurrentDate("yyyyMMdd"))) {
					break;
				}
				if (format.format(i).equals(end))
					break;
				if (format.format(i).equals(
						end.substring(0, 6)
								+ Util.addZero(Util.getDayByMouth(end
										.substring(0, 6))
										+ "", 2)))
					break;
				Calendar theCa = Calendar.getInstance();
				theCa.setTime(i);
				theCa.add(theCa.DATE, 1);
				i = theCa.getTime();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				c.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return chars_tmp;
	}

	public List<Chars> getMonM(String start, String end) {
		String sql1 = "";
		String avg = "";
		String cx = "";// 撤销交易代码
		Hashtable tableT = new Hashtable();
		List tradeT = serviceMysql.listAll("CodeView");
		for (int i = 0; i < tradeT.size(); i++) {
			CodeView mc = (CodeView) tradeT.get(i);
			tableT.put(mc.getId().getCodeid(), "[" + mc.getId().getBankName()
					+ "] " + mc.getId().getCodename());
			if (mc.getId().getCodename().indexOf("撤销") != -1
					|| mc.getId().getCodename().indexOf("退货") != -1) {
				cx += "'" + mc.getId().getCodeid() + "',";
			}
		}
		cx += "''";
		DateFormat format = new SimpleDateFormat("yyyyMMdd");
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String tablename = m.getTableName();
		String tablename2 = tablename.replace("_", "");
		Hashtable ta = new Hashtable();
		Hashtable old = new Hashtable();
		if (m.getIsAll() == 0) {
			String ss = (String) getRequest().getSession().getAttribute(
					"termgroup");
			if (ss.indexOf("C") != -1)
				ss = ss.replace("C", "");
			sql1 = " and ( termid in (" + ss + ") or ORG_TERMCDE in (" + ss
					+ ") or  OUTCDNO in (" + ss
					+ ") or substr(INTCDNO,5) in  (" + ss + "))";
			avg = " and ( termid in (" + ss + ") or ORG_TERMCDE in (" + ss
					+ ") or  OUTCDNO in (" + ss
					+ ") or substr(INTCDNO,5) in  (" + ss + "))";
		}
		List chars_tmp = new ArrayList<Chars>();

		String sql = "";
		sql += "select pepdate,sum(case when trancde in ("
				+ cx
				+ ") then concat('-',tranamt) else tranamt end )/100 from "
				+ tablename
				+ " t where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%')and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ sql1
				+ " and (FST_SDRESPN = '00' or ORG_APRESPN = '00')  and (pepdate between '"
				+ start + "' and '" + end + "') group by pepdate";
		Connection c = service.getConnection();
		try {
			LOG.info(sql);
			PreparedStatement ps = c.prepareStatement(sql);
			ResultSet rset = ps.executeQuery();
			while (rset.next()) {
				ta.put(rset.getString(1).substring(6), rset.getString(2));
			}
			String s = start.substring(0, 6);
			sql = sql.replace(s, Integer.valueOf(s) - 1 + "");
			// sql = sql.replace("Wt_Pep_Jnls_Bak", "Wt_Pep_Jnls_Bak_2009");
			LOG.info(sql);
			ps = c.prepareStatement(sql);
			rset = ps.executeQuery();
			while (rset.next()) {
				old.put(rset.getString(1).substring(6), rset.getString(2));
			}
			Date i = format.parse(start);
			while (true) {
				String d = format.format(i).substring(6);
				Chars cc = new Chars();
				String o = (String) old.get(d);
				String n = (String) ta.get(d);
				cc.setName(d + "日");
				if (o != null)
					cc.setViews(o);
				else
					cc.setViews("0");
				if (n != null)
					cc.setVisits(n);
				else
					cc.setVisits("0");
				cc.setLevel("0");
				if (o != null && n != null && level != null
						&& !level.equals("0")) {
					double oo = Double.valueOf(o);
					double nn = Double.valueOf(n);
					double lev = (nn - oo) / oo * 100;

					if (lev < 0) {
						lev = Math.abs(lev);
						if (Double.valueOf(level) < lev) {
							cc.setLevel(n);
						} else
							cc.setLevel("0");
					}
				}
				cc.setPre(start.substring(0, 4));
				chars_tmp.add(cc);
				if (format.format(i).equals(Util.getCurrentDate("yyyyMMdd"))) {
					break;
				}
				if (format.format(i).equals(end))
					break;
				if (format.format(i).equals(
						end.substring(0, 6)
								+ Util.addZero(Util.getDayByMouth(end
										.substring(0, 6))
										+ "", 2)))
					break;
				Calendar theCa = Calendar.getInstance();
				theCa.setTime(i);
				theCa.add(theCa.DATE, 1);
				i = theCa.getTime();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				c.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return chars_tmp;
	}

	// demo演示
	public List<Chars> getMouthTrade_demo(String start, String end,
			String shopid) {
		String sql1 = "";
		String avg = "";
		DateFormat format = new SimpleDateFormat("yyyyMMdd");
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String tablename = "tmp_mer";
		Hashtable ta = new Hashtable();
		if (shopid != null) {
			sql1 = " and mercode = '" + shopid + "' ";
			avg = " and mercode = '" + shopid + "' ";
		}
		List chars_tmp = new ArrayList<Chars>();

		String sql = "";
		avg = "select FLOOR(nvl(AVG(t.tranamt),0)) from "
				+ tablename
				+ " t where (tranamt between '000000000001' and '999999999999')and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ avg + " and (pepdate between '" + start + "' and '" + end
				+ "') ";
		sql += "select trandate,count(*),sum(t.tranamt) from "
				+ tablename
				+ " t where (tranamt between '000000000001' and '999999999999')and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ sql1 + "  and (trandate between '" + start + "' and '" + end
				+ "') group by trandate";
		Connection c = service.getConnection();
		try {
			LOG.info(sql);
			PreparedStatement ps = c.prepareStatement(sql);
			ResultSet rset = ps.executeQuery();
			long cou = 0;
			long su = 0;
			long pr = 0;
			while (rset.next()) {
				cou += Long.valueOf(rset.getString(2));
				if (rset.getString(3).indexOf(".") != -1
						&& !rset.getString(3).equals(""))
					su += Long.valueOf(rset.getString(3).substring(0,
							rset.getString(3).indexOf(".")));
				else
					su += Long.valueOf(rset.getString(3));
				ta.put(rset.getString(1).substring(6), rset.getString(3) + ","
						+ rset.getString(2));
			}
			if (su == 0 || cou == 0) {

			} else {
				pr = su / cou / 2;
			}
			Date i = format.parse(start);
			while (true) {
				String d = format.format(i).substring(6);
				Chars cc = new Chars();
				if (ta.get(d) != null) {
					String data = (String) ta.get(d);
					cc.setName(d + "日");
					// System.out.println(data);
					cc.setViews(data.split(",")[0]);
					cc.setVisits(Integer.valueOf(data.split(",")[1]) * pr + "");
				} else {
					cc.setName(d + "日");
					cc.setViews("0");
					cc.setVisits("0");
				}
				cc.setPre(pr + "");
				chars_tmp.add(cc);
				if (format.format(i).equals(Util.getCurrentDate("yyyyMMdd"))) {
					break;
				}
				if (format.format(i).equals(end))
					break;
				if (format.format(i).equals(
						end.substring(0, 6)
								+ Util.addZero(Util.getDayByMouth(end
										.substring(0, 6))
										+ "", 2)))
					break;
				Calendar theCa = Calendar.getInstance();
				theCa.setTime(i);
				theCa.add(theCa.DATE, 1);
				i = theCa.getTime();
			}
			c.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return chars_tmp;
	}

	public List<Chars> getYearTrade_demo(String start, String end, String shopid) {
		String sql1 = "";
		String avg = "";
		DateFormat format = new SimpleDateFormat("yyyyMMdd");
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String tablename = "tmp_mer";
		Hashtable ta = new Hashtable();
		if (shopid != null) {
			sql1 = " and mercode = '" + shopid + "' ";
			avg = " and mercode = '" + shopid + "' ";
		}
		List chars_tmp = new ArrayList<Chars>();
		String sql = "";
		avg = "select FLOOR(AVG(t.tranamt)) from "
				+ tablename
				+ " t where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%')and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ avg + " and (trandate between '" + start.substring(0, 6)
				+ "01' and '" + end.substring(0, 6) + "31') ";
		sql += "select substr(trandate,0,6),count(*),sum(t.tranamt) from "
				+ tablename
				+ " t where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%')and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ sql1 + " and (trandate between '" + start.substring(0, 6)
				+ "01' and '" + end.substring(0, 6)
				+ "31') group by substr(trandate,0,6)";
		Connection c = service.getConnection();
		try {
			// LOG.info(avg);
			// PreparedStatement ps = c.prepareStatement(avg);
			// ResultSet rset = ps.executeQuery();
			// rset.next();
			// String str = rset.getString(1);
			// str = Integer.valueOf(str) / 2 + "";
			// sql = sql.replace("ABC", "10000");
			LOG.info(sql);
			PreparedStatement ps = c.prepareStatement(sql);
			ResultSet rset = ps.executeQuery();
			long cou = 0;
			long su = 0;
			long pr = 0;
			while (rset.next()) {
				cou += Long.valueOf(rset.getString(2));
				if (rset.getString(3).indexOf(".") != -1
						&& !rset.getString(3).equals("")) {
					String tmp = rset.getString(3).substring(0,
							rset.getString(3).indexOf("."));
					if (tmp.equals(""))
						tmp = "0";
					su += Long.valueOf(tmp);
				} else
					su += Long.valueOf(rset.getString(3));
				ta.put(rset.getString(1), rset.getString(3) + ","
						+ rset.getString(2));
			}
			if (su != 0) {
				pr = su / cou / 2;
			}
			Date i = format.parse(start);
			while (true) {
				String d = format.format(i).substring(0, 6);
				Chars cc = new Chars();
				if (ta.get(d) != null) {
					String data = (String) ta.get(d);
					cc.setName(d + "月");
					cc.setViews(data.split(",")[0]);
					cc.setVisits(Integer.valueOf(data.split(",")[1]) * pr + "");
				} else {
					cc.setName(d + "月");
					cc.setViews("0");
					cc.setVisits("0");
				}
				cc.setPre(pr + "");
				chars_tmp.add(cc);
				if (format.format(i).substring(0, 6).equals(
						Util.getCurrentDate("yyyyMM"))) {
					break;
				}
				if (format.format(i).substring(0, 6)
						.equals(end.substring(0, 6)))
					break;
				Calendar theCa = Calendar.getInstance();
				theCa.setTime(i);
				theCa.add(theCa.MONTH, 1);
				i = theCa.getTime();
			}
			c.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return chars_tmp;
	}

	public List<Chars> getMonM_demo(String start, String end, String shopid) {
		String sql1 = "";
		String avg = "";
		DateFormat format = new SimpleDateFormat("yyyyMMdd");
		UserView m = (UserView) getRequest().getSession().getAttribute(
				"mpay_user");
		String tablename = "tmp_mer";
		Hashtable ta = new Hashtable();
		Hashtable old = new Hashtable();
		if (shopid != null) {
			sql1 = " and mercode = '" + shopid + "' ";
			avg = " and mercode = '" + shopid + "' ";
		}
		List chars_tmp = new ArrayList<Chars>();

		String sql = "";
		sql += "select trandate,sum(t.tranamt) from "
				+ tablename
				+ " t where ((TRANAMT between '000000000001' and '999999999999') or TRANCDE like 'NP%')and  (ORG_APRESPN is not null and ORG_APRESPN!='FF') and MSGTYPE != '0400' and (trancde != 'ZRR' and trancde != 'WOV' and trancde !='11L') "
				+ sql1 + "  and (trandate between '" + start + "' and '" + end
				+ "') group by trandate";
		Connection c = service.getConnection();
		try {
			LOG.info(sql);
			PreparedStatement ps = c.prepareStatement(sql);
			ResultSet rset = ps.executeQuery();
			while (rset.next()) {
				ta.put(rset.getString(1).substring(6), rset.getString(2));
			}
			String s = start.substring(0, 6);
			sql = sql.replace(s, Integer.valueOf(s) - 1 + "");
			// sql = sql.replace("Wt_Pep_Jnls_Bak", "Wt_Pep_Jnls_Bak_2009");
			LOG.info(sql);
			ps = c.prepareStatement(sql);
			rset = ps.executeQuery();
			while (rset.next()) {
				old.put(rset.getString(1).substring(6), rset.getString(2));
			}
			Date i = format.parse(start);
			while (true) {
				String d = format.format(i).substring(6);
				Chars cc = new Chars();
				String o = (String) old.get(d);
				String n = (String) ta.get(d);
				cc.setName(d + "日");
				if (o != null)
					cc.setViews(o);
				else
					cc.setViews("0");
				if (n != null)
					cc.setVisits(n);
				else
					cc.setVisits("0");
				cc.setLevel("0");
				if (o != null && n != null && level != null
						&& !level.equals("0")) {
					double oo = Double.valueOf(o);
					double nn = Double.valueOf(n);
					double lev = (nn - oo) / oo * 100;

					if (lev < 0) {
						lev = Math.abs(lev);
						if (Double.valueOf(level) < lev) {
							cc.setLevel(n);
						} else
							cc.setLevel("0");
					}
				}
				cc.setPre(start.substring(0, 4));
				chars_tmp.add(cc);
				if (format.format(i).equals(Util.getCurrentDate("yyyyMMdd"))) {
					break;
				}
				if (format.format(i).equals(end))
					break;
				if (format.format(i).equals(
						end.substring(0, 6)
								+ Util.addZero(Util.getDayByMouth(end
										.substring(0, 6))
										+ "", 2)))
					break;
				Calendar theCa = Calendar.getInstance();
				theCa.setTime(i);
				theCa.add(theCa.DATE, 1);
				i = theCa.getTime();
			}
			c.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return chars_tmp;
	}

	public String getTradefee(String src, MpayTradeFee fee) {
		LOG.info("计算交易手续费...");
		String payFee = "0";
		try {
			Float amt = Float.valueOf(src);
			Float f1 = Float.valueOf(fee.getFactor1());
			Float f2 = Float.valueOf(fee.getFactor2());
			Float f3 = Float.valueOf(fee.getFactor3());
			System.out.println(fee.getProportion1() + " "
					+ fee.getProportion2() + " " + fee.getProportion3());
			Float fees = (float) 0;
			if (fee.getFeesType().equals("01")) {// 比例计算
				if (f1 == 0) {
					// 使用条件1
					fees = amt * Float.valueOf(fee.getProportion1());
					if (!fee.getProportionUp1().equals("0")) {
						if (fees > Float.valueOf(fee.getProportionUp1())) {
							fees = Float.valueOf(fee.getProportionUp1());
						}
					}
				} else if (f2 == 0) {
					if (amt > f1) {
						// 使用条件2
						fees = amt * Float.valueOf(fee.getProportion2());
						if (!fee.getProportionUp2().equals("0")) {
							if (fees > Float.valueOf(fee.getProportionUp2())) {
								fees = Float.valueOf(fee.getProportionUp2());
							}
						}
					} else {
						// 使用条件1
						fees = amt * Float.valueOf(fee.getProportion1());
						if (!fee.getProportionUp1().equals("0")) {
							if (fees > Float.valueOf(fee.getProportionUp1())) {
								fees = Float.valueOf(fee.getProportionUp1());
							}
						}
					}
				} else if (f3 == 0) {
					if (amt > f2) {
						// 使用条件3
						fees = amt * Float.valueOf(fee.getProportion3());
						if (!fee.getProportionUp3().equals("0")) {
							if (fees > Float.valueOf(fee.getProportionUp3())) {
								fees = Float.valueOf(fee.getProportionUp3());
							}
						}
					} else if (amt > f1) {
						// 使用条件2
						fees = amt * Float.valueOf(fee.getProportion2());
						if (!fee.getProportionUp2().equals("0")) {
							if (fees > Float.valueOf(fee.getProportionUp2())) {
								fees = Float.valueOf(fee.getProportionUp2());
							}
						}
					} else {
						// 使用条件1
						fees = amt * Float.valueOf(fee.getProportion1());
						if (!fee.getProportionUp1().equals("0")) {
							if (fees > Float.valueOf(fee.getProportionUp1())) {
								fees = Float.valueOf(fee.getProportionUp1());
							}
						}
					}
				} else {
					if (amt > f3) {
						// 不计算
						return payFee;
					} else {
						if (amt > f2) {
							// 使用条件3
							fees = amt * Float.valueOf(fee.getProportion3());
							if (!fee.getProportionUp3().equals("0")) {
								if (fees > Float
										.valueOf(fee.getProportionUp3())) {
									fees = Float
											.valueOf(fee.getProportionUp3());
								}
							}
						} else if (amt > f1) {
							// 使用条件2
							fees = amt * Float.valueOf(fee.getProportion2());
							if (!fee.getProportionUp2().equals("0")) {
								if (fees > Float
										.valueOf(fee.getProportionUp2())) {
									fees = Float
											.valueOf(fee.getProportionUp2());
								}
							}
						} else {
							// 使用条件1
							fees = amt * Float.valueOf(fee.getProportion1());
							if (!fee.getProportionUp1().equals("0")) {
								if (fees > Float
										.valueOf(fee.getProportionUp1())) {
									fees = Float
											.valueOf(fee.getProportionUp1());
								}
							}
						}
					}
				}
				if (fees < 0.01)
					fees = (float) 0;
				payFee = fees + "";

			} else if (fee.getFeesType().equals("02")) {// 固定计算
				if (f1 == 0) {
					// 使用条件1
					fees = Float.valueOf(fee.getProportion1());
				} else if (f2 == 0) {
					if (amt > f1) {
						// 使用条件2
						fees = Float.valueOf(fee.getProportion2());
					} else {
						// 使用条件1
						fees = Float.valueOf(fee.getProportion1());
					}
				} else if (f3 == 0) {
					if (amt > f2) {
						// 使用条件3
						fees = Float.valueOf(fee.getProportion3());
					} else if (amt > f1) {
						// 使用条件2
						fees = Float.valueOf(fee.getProportion2());
					} else {
						// 使用条件1
						fees = Float.valueOf(fee.getProportion1());
					}
				} else {
					if (amt > f3) {
						// 不计算
						return payFee;
					} else {
						if (amt > f2) {
							// 使用条件3
							fees = Float.valueOf(fee.getProportion3());
						} else if (amt > f1) {
							// 使用条件2
							fees = Float.valueOf(fee.getProportion2());
						} else {
							// 使用条件1
							fees = Float.valueOf(fee.getProportion1());
						}
					}
				}
				if (fees < 0.01)
					fees = (float) 0;
				payFee = fees + "";
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "0";
		}
		BigDecimal mData;
		if (payFee.substring(payFee.indexOf(".")).length() > 5) {
			mData = new BigDecimal(payFee)
					.setScale(3, BigDecimal.ROUND_HALF_UP);
			mData = mData.setScale(2, BigDecimal.ROUND_HALF_UP);
		} else {
			mData = new BigDecimal(payFee)
					.setScale(2, BigDecimal.ROUND_HALF_UP);
		}
		System.out.println(src + "_" + payFee + "_" + mData);
		return mData + "";
	}

}
